- 여기서 더 만들어야할까? 예약체계이다. (+리뷰)
- 예약체계를 만들어야하는데 예약은 유저와 호텔에 의존적이다.
호텔을 만들고 에약체계를 만들면 된다. 리뷰는 예약에 의존적이다.
호텔에는 객실타입이 있고 객실타입은 호텔에 의존적이다.
-> hotels 테이블 생성
-> Hotels INSERT
-> hotel_room_types 테이블 생성
-> hotel_room_types INSERT
-> hotel_room_charges 테이블 생성
-> hotel_room_charges INSERT
SELECT `charge`.`date` AS `투숙 일자`, `hotel`.`name` AS `호텔이름`, `room_type`.`name` AS `객실 이름`, (`charge`.`charge`) * (`room_type`.`price`) AS `숙박비용` FROM `site`.`hotel_room_types` AS `room_type` LEFT JOIN `site`.`hotel_room_charges` AS `charge` ON `room_type`.`hotel_index` = `charge`.`hotel_index` and `room_type`.`index` = `charge`.`hotel_room_type_index` LEFT JOIN `site`.`hotels` AS `hotel` ON `room_type`.`hotel_index` = `hotel`.`index` WHERE charge.`hotel_index` = 4 AND `room_type`.`index` = 6;
예제1)
SELECT '호텔이름' AS `항목`,
`hotel`.`name` AS `값`
FROM `site`.`hotels` AS `hotel`
WHERE `hotel`.`index` = 4
UNION
SELECT '방 이름' AS `항목`,
`type`.`name` AS `값`
FROM `site`.`hotel_room_types` AS `type`
WHERE `type`.`index` = 6
UNION
SELECT '체크인' AS `항목`,
`charge`.`date` AS `값`
FROM `site`.`hotel_room_charges` AS `charge`
WHERE `charge`.`date` = '2022-09-08'
UNION
SELECT '체크아웃' AS `항목`,
DATE_ADD(`charge`.`date`, INTERVAL 4 DAY ) AS `값`
FROM `site`.`hotel_room_charges` AS `charge`
WHERE `charge`.`date` = '2022-09-08'
UNION
SELECT '투숙일(박)' AS `항목`,
CONCAT((DATE_ADD(`charge`.`date`, INTERVAL 4 DAY )) - (`charge`.`date`) , '박') AS `값`
FROM `site`.`hotel_room_charges` AS `charge`
WHERE `charge`.`date` = '2022-09-08'
UNION
SELECT '숙박비용' AS `항목`,
SUM(`sumprice`.`perprice`) AS `값`
FROM (SELECT (charge.`charge`) * (`type`.`price`) AS `perprice`
FROM `site`.`hotel_room_types` AS `type`
LEFT JOIN `site`.`hotel_room_charges` AS `charge`
ON `type`.`hotel_index` = `charge`.`hotel_index` and `type`.`index` = `charge`.`hotel_room_type_index`
WHERE charge.`hotel_index` = 4
AND `charge`.`hotel_room_type_index` = 6
AND `charge`.`date` >= '2022-09-08') AS `sumprice`;-> reservations 테이블 생성
`CONSTRAINT CHECK (`from` < `to`)`1차로 js에서 2차로 서비스에서 3차로 Db에서 막아주면 더 좋기 때문에 해주자ㅎ
- insert!
예제2)
SELECT `user`.`name` AS `예약자 성명`, `user`.`email` AS `예약자 이메일`, CONCAT(`user`.`contact_first`, '-', `user`.`contact_second`, '-', `user`.`contact_third`) AS `예약자 연락처`, `type`.`name` AS `객실`, `reservation`.`from` AS `체크인`, `reservation`.`to` AS `체크아웃`, DATEDIFF(`reservation`.`to`, `reservation`.`from`) AS `투숙일(박)`, SUM(`charge`.`charge`) * (`type`.`price`) AS `숙박비용` FROM `site`.`reservations` AS `reservation` LEFT JOIN `site`.`users` AS `user` ON `reservation`.`user_index` = `user`.`index` LEFT JOIN `site`.`hotel_room_types` AS `type` ON `reservation`.`hotel_index` = `type`.`hotel_index` AND `reservation`.`hotel_room_type_index` = `type`.`index` LEFT JOIN `site`.`hotel_room_charges` AS `charge` ON `type`.`hotel_index` = `charge`.`hotel_index` AND `type`.`index` = `charge`.`hotel_room_type_index` AND `charge`.`date` >= `reservation`.`from` AND `charge`.`date` < `reservation`.`to` WHERE `reservation`.`index` = 5;
- 그 전 예제와 숙박 가격을 가져오는 메커니즘은 같은데 이번에는 리터럴을 사용하지 않고 가져와봤다.
IF(x, t, f)
x 조건이 참이면 t를, 아니라면 f를 반환한다.
SELECT IF(1 = 1 , '맞음','아님');: 맞음
IFNULL(v,a)
v값이 null이면 a값을 대신 사용한다.
SELECT IF(null, '저런');: 저런
예제 2) 날짜만 바꾸었을 때 값이 나오도록 하시오.
SELECT `hotel`.`name` AS `호텔명`,
`type`.`name` AS `객실명`,
`type`.`count` - COUNT(`reservation`.`hotel_room_type_index`) AS `가용 객실`,
IF(`type`.`count` - COUNT(`reservation`.`hotel_room_type_index`) = 0, '이용 불가',
FLOOR(`type`.`price` * `charge`.`charge`)) AS `숙박비용`
FROM `site`.`hotel_room_types` AS `type`
LEFT JOIN `site`.`hotels` AS `hotel` ON `type`.`hotel_index` = `hotel`.`index`
LEFT JOIN `site`.`hotel_room_charges` AS `charge`
ON type.hotel_index = charge.hotel_index
AND type.`index` = charge.hotel_room_type_index
AND `charge`.`date` = '2022-09-06'
LEFT JOIN `site`.`reservations` AS `reservation` ON `type`.`hotel_index` = `reservation`.`hotel_index` AND
`type`.`index` = `reservation`.`hotel_room_type_index` AND
`charge`.`date` < `reservation`.`to`
GROUP BY `type`.`index`;
- 2022-09-06 select
- 2022-09-11 select 날짜만 바꾸었을 때 이러한 결과가 나오면 된다.
-> 또 다른 방법도 있다.
SELECT `hotel`.`name` AS `호텔명`,
`type`.`name` AS `객실명`,
`type`.`count` - COUNT(`reservation`.`index`) AS `가용 객실`,
IF(`type`.`count` - COUNT(`reservation`.`index`) = 0, '이용 불가',
ROUND(`type`.`price` * `charge`.`charge`)) AS `숙박비용`
FROM `site`.`hotel_room_types` AS `type`
LEFT JOIN `site`.`hotels` AS `hotel` ON `type`.`hotel_index` = `hotel`.`index`
LEFT JOIN `site`.`reservations` AS `reservation` ON
`type`.`hotel_index` = `reservation`.`hotel_index` AND
`type`.`index` = `reservation`.`hotel_room_type_index` AND
'2022-09-11' >= `reservation`.`from` AND
'2022-09-11' < `reservation`.`to`
LEFT JOIN `site`.`hotel_room_charges` AS `charge`
ON `type`.hotel_index = charge.hotel_index AND
`type`.`index` = charge.hotel_room_type_index AND
`charge`.`date` = '2022-09-11'
GROUP BY `type`.`index`;
Software Developer : -)