MySQL案例室可用性 [英] MySQL Case Room Availability

查看：36
本文介绍了MySQL案例室可用性的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

我有桌子:预订，预订，房间，住宿信息

i have tables : booking, reservation, room , stayed_information

样本数据:好的首先抱歉加密样本数据给您带来的不便

sample data : Okay first sorry for inconvenience from the encryption sample data

这是与 room_type 相关的表

this is the table that relational with room_type

   mysql> SELECT
        -> stay_info.room, room.code
        -> FROM stay_info
        -> RIGHT JOIN t_room
        -> ON stay_info.room = room.code
        -> INNER JOIN room_type
        -> ON room_type.code = room.roomType
        -> WHERE room_type.name = "Deluxe Double";




+--------------------------------------+--------------------------------------+
| room                                 | code                                 |
+--------------------------------------+--------------------------------------+
| NULL                                 | 26a73433-d0cc-4e93-95d9-453d362e85a7 |
| NULL                                 | 2b166d4f-2fe4-404c-beff-482c7d81c103 |
| NULL                                 | 3efc3bff-9c02-43ef-a494-e887a342c1f5 |
| NULL                                 | 7e0ebe37-a23a-46b6-9351-ba0c952ed33e |
| NULL                                 | 9574eb5f-58de-427f-859e-d8b61e289836 |
| NULL                                 | 9ee50e45-f92b-46bd-bf3e-fc818a96a81f |
| NULL                                 | d72f3f7e-c9d2-44d8-8767-66d2a1477a1b |
| NULL                                 | e25587a3-3dc1-4c0f-b4c2-68a1da538bd7 |
| NULL                                 | e2d7fe3a-06e2-48df-a083-6c8eaeeefc22 |
| NULL                                 | fadc4d40-33b2-4545-98fe-e52d351c50f9 |
+--------------------------------------+--------------------------------------+

10 行(0.00 秒)

10 rows in set (0.00 sec)

如果我有预订流程

mysql> SELECT
    -> stay_info.room, IF(reservation.`status` = 1, room.code, (IF (stay
_info.room IS null , room.code,null))) as code
    -> FROM stay_info
    -> INNER JOIN reservation
    -> ON reservation.stayInfo = stay_info.code
    -> RIGHT JOIN room
    -> ON stay_info.room = room.code
    -> INNER JOIN room_type
    -> ON room_type.code = room.roomType
    -> WHERE room_type.name = "Deluxe Double" ;





+--------------------------------------+--------------------------------------+
| room                                 | code                                 |
+--------------------------------------+--------------------------------------+
| NULL                                 | 26a73433-d0cc-4e93-95d9-453d362e85a7 |
| NULL                                 | 2b166d4f-2fe4-404c-beff-482c7d81c103 |
| NULL                                 | 3efc3bff-9c02-43ef-a494-e887a342c1f5 |
| NULL                                 | 7e0ebe37-a23a-46b6-9351-ba0c952ed33e |
| NULL                                 | 9574eb5f-58de-427f-859e-d8b61e289836 |
| NULL                                 | 9ee50e45-f92b-46bd-bf3e-fc818a96a81f |
| NULL                                 | d72f3f7e-c9d2-44d8-8767-66d2a1477a1b |
| e25587a3-3dc1-4c0f-b4c2-68a1da538bd7 | NULL                                 |
| NULL                                 | e2d7fe3a-06e2-48df-a083-6c8eaeeefc22 |
| NULL                                 | fadc4d40-33b2-4545-98fe-e52d351c50f9 |
+--------------------------------------+--------------------------------------+

10 行(0.00 秒)

10 rows in set (0.00 sec)

所以 room.code 字段是可用房间.预订.status = 1 表示预订无效，因此它将是可用房间，因为激活时的预订状态为 0，因此 room.code 为 NULL

so room.code field is the available room. reservation.status = 1 means that booking have void so it will be the available room, because status booking when active is 0 so room.code is NULL

同样发生在预订详情中

   mysql> SELECT
        -> stay_info.room, IF(booking_details.`status` = 1, room.code, (IF (stay_info.room IS null , room.code,null))) as code
        -> FROM stay_info
        -> INNER JOIN booking_details
        -> ON booking_details.stayInfo = stay_info.code
        -> RIGHT JOIN room
        -> ON stay_info.room = room.code
        -> INNER JOIN room_type
        -> ON room_type.code = room.roomType
        -> WHERE room_type.name = "Deluxe Double" ;





+--------------------------------------+--------------------------------------+
| room                                 | code                                 |
+--------------------------------------+--------------------------------------+
| 26a73433-d0cc-4e93-95d9-453d362e85a7 | NULL                                 |
| NULL                                 | 2b166d4f-2fe4-404c-beff-482c7d81c103 |
| NULL                                 | 3efc3bff-9c02-43ef-a494-e887a342c1f5 |
| NULL                                 | 7e0ebe37-a23a-46b6-9351-ba0c952ed33e |
| NULL                                 | 9574eb5f-58de-427f-859e-d8b61e289836 |
| NULL                                 | 9ee50e45-f92b-46bd-bf3e-fc818a96a81f |
| NULL                                 | d72f3f7e-c9d2-44d8-8767-66d2a1477a1b |
| NULL                                 | e25587a3-3dc1-4c0f-b4c2-68a1da538bd7 |
| NULL                                 | e2d7fe3a-06e2-48df-a083-6c8eaeeefc22 |
| NULL                                 | fadc4d40-33b2-4545-98fe-e52d351c50f9 |
+--------------------------------------+--------------------------------------+

10 行(0.00 秒)

10 rows in set (0.00 sec)

当我将上面的两个关系表与内连接结合起来时

when i combine two relation tables above with inner join 

mysql> SELECT
-> stay_info.room,
-> IF((booking_details.`status` = 1 or reservation.`status` = 1), room.code, (IF (stay_info.room IS null , room.code,null))) as code
-> FROM stay_info
-> INNER JOIN booking_details
-> ON booking_details.stayInfo = stay_info.code
-> INNER JOIN reservation
-> ON reservation.stayInfo = stay_info.code
-> RIGHT JOIN room
-> ON stay_info.room = room.code
-> INNER JOIN room_type
-> ON room_type.code = room.roomType
-> WHERE room_type.name = "Deluxe Double" ;





+------+--------------------------------------+
| room | code                                 |
+------+--------------------------------------+
| NULL | 26a73433-d0cc-4e93-95d9-453d362e85a7 |
| NULL | 2b166d4f-2fe4-404c-beff-482c7d81c103 |
| NULL | 3efc3bff-9c02-43ef-a494-e887a342c1f5 |
| NULL | 7e0ebe37-a23a-46b6-9351-ba0c952ed33e |
| NULL | 9574eb5f-58de-427f-859e-d8b61e289836 |
| NULL | 9ee50e45-f92b-46bd-bf3e-fc818a96a81f |
| NULL | d72f3f7e-c9d2-44d8-8767-66d2a1477a1b |
| NULL | e25587a3-3dc1-4c0f-b4c2-68a1da538bd7 |
| NULL | e2d7fe3a-06e2-48df-a083-6c8eaeeefc22 |
| NULL | fadc4d40-33b2-4545-98fe-e52d351c50f9 |
+------+--------------------------------------+

10 行(0.00 秒)

10 rows in set (0.00 sec)

我对组合的期望是

+--------------------------------------+--------------------------------------+
| room                                 | code                                 |
+--------------------------------------+--------------------------------------+
| 26a73433-d0cc-4e93-95d9-453d362e85a7 | NULL                                 |
| NULL                                 | 2b166d4f-2fe4-404c-beff-482c7d81c103 |
| NULL                                 | 3efc3bff-9c02-43ef-a494-e887a342c1f5 |
| NULL                                 | 7e0ebe37-a23a-46b6-9351-ba0c952ed33e |
| NULL                                 | 9574eb5f-58de-427f-859e-d8b61e289836 |
| NULL                                 | 9ee50e45-f92b-46bd-bf3e-fc818a96a81f |
| NULL                                 | d72f3f7e-c9d2-44d8-8767-66d2a1477a1b |
| e25587a3-3dc1-4c0f-b4c2-68a1da538bd7 | NULL                                 |
| NULL                                 | e2d7fe3a-06e2-48df-a083-6c8eaeeefc22 |
| NULL                                 | fadc4d40-33b2-4545-98fe-e52d351c50f9 |
+--------------------------------------+--------------------------------------+

所以我会很容易地从代码字段中获得可用房间

so i will get easily the available room from code field

推荐答案

我找到了自己问题的解决方案这是解决方案

i've found solution of my own question this is the solution

选择 t_room_type.code, t_room_type.name,COUNT(t_room_type.name) ASnumRoom FROM t_room INNER JOIN t_room_type ON t_room.roomType =t_room_type.code WHERE t_room.code NOT IN (SELECT DISTINCTt_stay_info.room FROM t_stay_info LEFT OUTER JOIN t_reservation ONt_stay_info.code = t_reservation.stayInfo 左外连接t_booking_details ON t_stay_info.code = t_booking_details.stayInfo左外连接 t_walk_in ON t_stay_info.code = t_walk_in.stayInfo左外连接 t_stay_info_details ON t_stay_info.code =t_stay_info_details.stayInfo WHERE t_stay_info_details.stayDate'2014-07-16' 和 '2014-07-17' AND (t_reservation.status = '0'或 t_booking_details.status = '0' 或 t_walk_in.status = '0')) GROUP BYt_room_type.name ORDER BY t_room_type.name ASC

SELECT t_room_type.code, t_room_type.name,COUNT(t_room_type.name) AS numRoom FROM t_room INNER JOIN t_room_type ON t_room.roomType = t_room_type.code WHERE t_room.code NOT IN (SELECT DISTINCT t_stay_info.room FROM t_stay_info LEFT OUTER JOIN t_reservation ON t_stay_info.code = t_reservation.stayInfo LEFT OUTER JOIN t_booking_details ON t_stay_info.code = t_booking_details.stayInfo LEFT OUTER JOIN t_walk_in ON t_stay_info.code = t_walk_in.stayInfo LEFT OUTER JOIN t_stay_info_details ON t_stay_info.code = t_stay_info_details.stayInfo WHERE t_stay_info_details.stayDate BETWEEN '2014-07-16' AND '2014-07-17' AND (t_reservation.status = '0' OR t_booking_details.status = '0' OR t_walk_in.status = '0')) GROUP BY t_room_type.name ORDER BY t_room_type.name ASC

这篇关于MySQL案例室可用性的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！

查看全文
相关文章
数据库最新文章
热门教程
热门工具
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆