예약사이트 날짜검색&인원검색 방법 1 13 1,964

by Jonny [MySQL] [2019.01.11 15:11:16]


F-1.jpg (563,240Bytes)
1.PNG (51,830Bytes)

팀프로젝트로 게스트하우스 사이트를 만들고 있습니다.

6명이 들어갈 수 있는 방 A가 있다고 가정하면

하루하루 예약상황에 따라

1/15일은 3명만 예약가능

1/16일은 한명 체크아웃해서 4명 예약가능

1/17일은 두명 체크인해서 2명만 예약가능

 

이런식으로 하루하루 예약가능 상황을 체크해야하는데요

 이 내용을 처리할 때 어떤식으로 보통 진행하시나요??

 

강사님은 한 컬럼에 콤마 구분자를 써라

아니면 A방의 6개월치의 예약가능인원 테이블을 만들어라

 

라고 하시는데, 정확히 어떤 얘기인지는 모르겠습니다.

힌트라도 주시면 찾아보도록 하겠습니다. 읽어주셔서 감사합니다~

 

 

-----

Input - 검색한 체크인 날짜&체크아웃 날짜&예약할 인원수

Output - 검색된 날짜 안에서 예약할 인원수보다 자리가 많은 방을 하나라도 가지고있는 게스트하우스를 뽑아내어 정렬

by 소율파파 [2019.01.11 15:24:45]

체크인한 사용자 정보를 보여줘야 하는건가요? 아니면 체크인 사용자 인원 수만 알면 되나요?


by Jonny [2019.01.11 15:29:09]

사용자 정보는 보여주지 않아도 됩니다.

만약 3명을 검색 조건에 두었다고 한다면

(검색한 날짜에) 남아있는 인원수가 3명보다 많은지 적은지만 판단 가능하면 됩니다!


by 소율파파 [2019.01.11 15:33:51]

앗 그럼 체크인 할 때 현재 체크인 한 인원수 값만 갱신하면 되지 않나요?


by Jonny [2019.01.11 15:40:38]

체크인 한 인원수 값을 계산할 때 날짜정보가 들어있지 않다면

방테이블에 예약가능 인원수 컬럼 하나 추가하면 해결이 되는데요

 

1/13일 1/14일 1/15일 1/16일 1/17일 1/18일...

등등 날짜에 대한 정보도 같이 들어가기 때문에

하루하루에 대한 예약가능 인원을 어떻게 계산해야할지 조금 막막합니다.

 


by 우리집아찌 [2019.01.11 15:46:05]

단순하게 가려면

예약 테이블  - 날짜 , 게스트하우스번호, 방번호, 회원번호 

게스트룸 테이블 - 게스트하우스번호 , 방번호, 수용인원 

이런식으로 구성될것 같은데요.

 

 SELECT  A.게스트하우스 번호 , A.방번호 ,  COUNT(*) 예약인원 , MAX(B.수용인원) -  COUNT(*) 남는 수용인원

   FROM 예약테이블 A

         ,  게스트룸 테이블 B

  WHERE  A.게스트하우스번호  = B.게스트하우스번호

     AND  A.방번호                  = B.방번호

     AND  날짜 BETWEEN 체크인날짜 AND 체크아웃날짜

  GROUP BY A.게스트하우스 번호 , A.방번호

  HAVING  MAX(B.수용인원) -  COUNT(*) > 0

 


by 소율파파 [2019.01.11 15:54:13]

방코드, 입실날짜, 입실인원수로 구성된 입실현황 테이블을 만들면 될 거 같아요~


by Jonny [2019.01.11 16:03:48]

우리집아찌님, 소율파파님 답변 감사드립니다.

테스트하는 데에 시간이 조금 걸릴 것 같아요.

한번 해보고 다시 오도록 하겠습니다. 고맙습니다!!!


by 마농 [2019.01.11 16:17:21]
-- 달력 테이블 하나 만들어 사용하시면 편리합니다.
SELECT b.방코드
     , b.방이름
     , b.수용인원
     , a.일자
     , SUM(c.예약인원수) AS 예약인원수
     , b.수용인원 - NVL(SUM(c.예약인원수), 0) AS 예약가능인원
  FROM 달력 a
 CROSS JOIN 방 b
  LEFT OUTER JOIN 예약정보 c
    ON b.방코드 = c.방코드
   AND a.일자 BETWEEN c.입실예정일 AND c.퇴실예정일
 WHERE a.일자 BETWEEN  :입실예정일 AND  :퇴실예정일
 GROUP BY b.방코드, b.방이름, b.수용인원, a.일자
HAVING b.수용인원 - NVL(SUM(c.예약인원수), 0) >= :예약인원수
;

 


by 마농 [2019.01.11 16:24:28]
-- 조건 추가 --
SELECT b.방코드
     , b.방이름
     , b.수용인원
     , a.일자
     , SUM(c.예약인원수) AS 예약인원수
     , b.수용인원 - NVL(SUM(c.예약인원수), 0) AS 예약가능인원
  FROM (SELECT 일자
          FROM 달력
         WHERE 일자 BETWEEN  :입실예정일 AND  :퇴실예정일
        ) a
 CROSS JOIN
       (SELECT x.방코드, x.방이름, x.수용인원
          FROM 방 x
         INNER JOIN 게스트하우스 y
            ON x.게스트하우스코드 = y.게스트하우스코드
         WHERE 1=1
           AND -- 각종 조건들 --
        ) b
  LEFT OUTER JOIN 예약정보 c
    ON b.방코드 = c.방코드
   AND a.일자 BETWEEN c.입실예정일 AND c.퇴실예정일
 GROUP BY b.방코드, b.방이름, b.수용인원, a.일자
HAVING b.수용인원 - NVL(SUM(c.예약인원수), 0) >= :예약인원수
;

 


by Jonny [2019.01.11 18:51:26]

마농님 고맙습니다!!!!!!!!

오라클 NVL부분만 MYSQL로 수정해서 적용했습니다.

감사합니다!


by Jonny [2019.01.11 18:59:58]

1. 날짜 테이블 만들기 참고 - http://nekomimi.tistory.com/722

2. 샘플데이터 넣기

- 회원테이블

- 게스트하우스 테이블

- 방테이블

- 예약정보테이블

 

3. 답변주신 ORACLE SQL을 MySQL로 조금 수정

(2018-01-12 ~ 2018-01-14) 2명 검색한 경우

select b.roomcode,
  b.roomname,
    b.capacity,
     a.d,
    sum(c.bookingnumber) as "예약인원수"
     , b.capacity - ifnull(sum(c.bookingnumber),0) as "예약가능인원"
     from (select d
     from date_t
     where d between '2018-01-12' and '2018-01-14'
     ) a
     cross join
     (select x.roomcode, x.roomname, x.capacity
     from room_tb x
     inner join guest_tb y
     on x.guesthousecode = y.guesthousecode
     where 1=1
     ) b
     left outer join booking_tb c
     on b.roomcode = c.roomcode
     and a.d between c.bookingstart and c.bookingend
     group by b.roomcode, b.roomname, b.capacity, a.d
     having b.capacity - ifnull(SUM(c.bookingnumber),0) >= 2;

 


by 마농 [2019.01.14 09:51:51]

1. 날짜 테이블 만들기 참고 - http://gurubee.net/article/65315
2. 알리아스에 홑따옴표 사용은 표준이 아닙니다. ==> 쌍따옴표 사용하세요.


by Jonny [2019.01.29 17:13:39]

마농님 댓글을 너무 늦게 확인했네요 정말 감사합니다!

댓글등록
SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code() 버튼을 클릭하여 작성 하시면 됩니다.
로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입