酒店房间预订的sql查询

Question

我正在以 Windows 形式制作酒店预订系统。用户通过选择酒店 ID 并提供 check_in 来预订酒店房间。和 check_out日期。现在我想从 room_no 中检索房间号( tblRoom )他们不在的地方 tblReservation (我的意思是尚未预订的房间)，以及在 room_no 中的房间号( tblReservation )但不在 check_in 之间和 check_out日期。下面的代码允许我获得 room_id ，但我需要 room_no .

SqlCommand cmd = new SqlCommand(@"SELECT room_id FROM tblRoom WHERE (hotel_id=@hotel_id AND 
                room_id NOT IN (SELECT room_id FROM tblReservation)) union select room_id from tblReservation
                where (@endDate<check_in or @startDate>check_out) and hotel_id=@hotel_id", con);

这是我的表:

Answer 1

查询 #2 为您提供给定 hotel_id 的所有免费房间和一个 check in/check out日期(包括 in 和 out 日期，对于 05/n 到 10/n，您停留 6 天)

查询 #3 将为您提供具有先前参数的所有租用房间。

SQL Fiddle

MySQL 5.6 架构设置 :

CREATE TABLE TblReservation
    (`reservation_id` int, `hotel_id` int, `room_id` int, `check_in` date, `check_out` date)
;

INSERT INTO TblReservation
    (`reservation_id`, `hotel_id`, `room_id`, `check_in`, `check_out`)
VALUES
    (1, 1, 1, '2017-04-01', '2017-04-02'),
    (2, 1, 1, '2017-04-06', '2017-04-10'),
    (3, 1, 2, '2017-04-01', '2017-04-03'),
    (4, 1, 4, '2017-04-01', '2017-04-10'),
    (5, 2, 5, '2017-04-01', '2017-04-10')
;


CREATE TABLE TblRoom
    (`room_id` int, `hotel_id` int, `room_num` int)
;

INSERT INTO TblRoom
    (`room_id`, `hotel_id`, `room_num`)
VALUES
    (1, 1, 1100),
    (2, 1, 1200),
    (3, 1, 1300),
    (4, 1, 1400),
    (5, 2, 2500)
;

查询 1 :

set @hotel_id = 1, @check_in = '2017-04-03', @check_out = '2017-04-05'

查询 2 :

select TblRoom.* 
from TblRoom
left join TblReservation
    on TblRoom.hotel_id = TblReservation.hotel_id
        and TblRoom.room_id = TblReservation.room_id
        and TblReservation.check_out >=  @check_in
        and TblReservation.check_in <= @check_out
where
    TblRoom.hotel_id = @hotel_id
    and TblReservation.reservation_id IS NULL

Results :

| room_id | hotel_id | room_num |
|---------|----------|----------|
|       1 |        1 |     1100 |
|       3 |        1 |     1300 |

查询 3 :

select 
  TblRoom.*, 
  date_format(check_in,'%Y-%m-%d') check_in, 
  date_format(check_out,'%Y-%m-%d') check_out
from TblRoom
inner join TblReservation
    on TblRoom.hotel_id = TblReservation.hotel_id
        and TblRoom.room_id = TblReservation.room_id
        and TblReservation.check_out >=  @check_in
        and TblReservation.check_in <= @check_out
where
    TblRoom.hotel_id = @hotel_id

Results :

| room_id | hotel_id | room_num |   check_in |  check_out |
|---------|----------|----------|------------|------------|
|       2 |        1 |     1200 | 2017-04-01 | 2017-04-03 |
|       4 |        1 |     1400 | 2017-04-01 | 2017-04-10 |