最近在写一个酒店的app，现在把查询房间数的sql思路列出来，看看有没有啥问题

Question

用到的表有四张，关键字段如下：

酒店表

房间类型表

房间表

房间订单表，订单表会有逻辑限制，使得开始时间在开始日的下午2点以后，或者与结束日同一天的九点之前，结束时间统一在结束日中午12点

查询SQL思路如下

#第一步从订单表中查出结束时间在当前天结束点之后的订单，即可查出那些房间还在有客状态
select ro.roomId from room_order ro where ro.orderEndTime > concat(curdate(),' 12:00:00');

#第二步连接房间表查出那些房间没有订单，既那些房间可以接客
select 
  ro.roomId,rm.id,rm.roomName
from ( 
  select roomId from room_order where orderEndTime > concat(curdate(),' 12:00:00') 
) ro right join room rm on ro.roomId=rm.id where ro.roomId is null

#第三步可接客房间连接房间类型表和酒店表查出供客户下单用的房间类型列表
select 
    ho.hotelName,rc.roomClassName,count(rm.roomClassId) '剩余房间数'
from (
    select 
        rm.id,rm.roomClassId,rm.roomName
    from ( 
              select roomId from room_order where orderEndTime > concat(curdate(),' 12:00:00') 
         ) ro right join room rm on ro.roomId=rm.id where ro.roomId is null
) rm right join room_class rc on rm.roomClassId=rc.id 
right join hotel ho on rc.hotelId=ho.id 
group by rm.roomClassId

#第四步优化SQL查询出全部房间类型对应的房间数
select 
    ho.hotelName,rc.roomClassName,SUM(rm.cou) '剩余房间数'
from (
    select 
        case when ro.roomId is NULL then 1 when ro.roomId is not NULL then 0 end cou,rm.roomClassId
    from ( 
                select roomId from room_order where orderEndTime > concat(curdate(),' 12:00:00') 
          ) ro right join room rm on ro.roomId=rm.id 
) rm right join room_class rc on rm.roomClassId=rc.id 
right join hotel ho on rc.hotelId=ho.id 
group by rm.roomClassId

#第五步 查询出有客和无客的房间是哪些
select 
    case when ro.roomId is NULL then '无客' when ro.roomId is not NULL then '有客' end mes,ro.roomId,rm.id,rm.roomClassId,rm.roomName
from ( 
    select roomId from room_order where orderEndTime > concat(curdate(),' 12:00:00') 
) ro right join room rm on ro.roomId=rm.id

Answer 1

【以下回答由 GPT 生成】

根据给出的SQL查询思路，评估如下：

Step 1中的查询订单表的SQL语句是正确的，可以查询出在当前天结束点之后仍有客人入住的房间数。

Step 2中的连接房间表查询没有订单的房间的SQL语句也是正确的，可以查询出可以接待客人的房间数。

Step 3中连接房间类型表和酒店表的SQL语句也是正确的，可以查询出每个房间类型的剩余房间数。

Step 4中优化查询的SQL语句也是正确的，可以查询出全部房间类型对应的剩余房间数。

整体上，这个SQL查询的思路是正确的，能够查询到所需的剩余房间数。不过有一些地方可以进行优化：

1. 在Step 1中，使用CURDATE()函数来获取当前日期，这个函数会在每次执行查询时都返回当前日期，可以在查询之前先获取一次当前日期并保存，避免多次执行CURDATE()函数，提高查询效率。

2. 在Step 3和Step 4中，使用RIGHT JOIN连接表时，如果表之间的外键关系是存在的，可以改为使用INNER JOIN，避免使用不必要的RIGHT JOIN，提高查询效率。

关于以上提到的优化点，下面给出相应的代码示例：

-- Step 1: 获取当前日期并保存
SET @currentDate = CURDATE();

-- Step 2
SELECT 
  ro.roomId, rm.id, rm.roomName
FROM 
  (SELECT roomId FROM room_order WHERE orderEndTime > CONCAT(@currentDate, ' 12:00:00')) ro
  RIGHT JOIN room rm ON ro.roomId = rm.id
WHERE ro.roomId IS NULL;

-- Step 3
SELECT 
  ho.hotelName, rc.roomClassName, COUNT(rm.roomClassId) AS '剩余房间数'
FROM 
  (
    SELECT 
      rm.id, rm.roomClassId, rm.roomName
    FROM 
      (SELECT roomId FROM room_order WHERE orderEndTime > CONCAT(@currentDate, ' 12:00:00')) ro
      RIGHT JOIN room rm ON ro.roomId = rm.id
    WHERE ro.roomId IS NULL
  ) rm
  INNER JOIN room_class rc ON rm.roomClassId = rc.id
  INNER JOIN hotel ho ON rc.hotelId = ho.id
GROUP BY rm.roomClassId;

-- Step 4
SELECT 
  ho.hotelName, rc.roomClassName, SUM(rm.cou) AS '剩余房间数'
FROM 
  (
    SELECT 
      CASE 
        WHEN ro.roomId IS NULL THEN 1
        WHEN ro.roomId IS NOT NULL THEN 0
      END AS cou, rm.roomClassId
    FROM 
      (
        SELECT 
          rm.id, rm.roomClassId, rm.roomName
        FROM 
          (SELECT roomId FROM room_order WHERE orderEndTime > CONCAT(@currentDate, ' 12:00:00')) ro
          RIGHT JOIN room rm ON ro.roomId = rm.id
        WHERE ro.roomId IS NULL
      ) rm
  ) rm
  INNER JOIN room_class rc ON rm.roomClassId = rc.id
  INNER JOIN hotel ho ON rc.hotelId = ho.id
GROUP BY rm.roomClassId;

以上是针对SQL查询思路的评估和优化建议，希望对你有所帮助。

【相关推荐】

• 帮你找了个相似的问题, 你可以看下: https://ask.csdn.net/questions/7716445
• 这篇博客也不错, 你可以看下【sql查询】使用sql查询一个物品是否在有效期内的方法（数据库无这个字段 通过生产日期和保质期进行计算得出）
• 您还可以看一下 铭智老师的一个完整网站的渗透测试课程中的 低危漏洞中发现了sql备份脚本小节, 巩固相关知识点
  
• 除此之外, 这篇博客: sql注入（一）中的 当我们进行注入的时候向数据库传递SQL语句进行数据库操作时，没有对我们所提交的参数做严格的过滤处理，直接输入特殊的sql语句就能来获取或修改数据库内容 部分也许能够解决你的问题。

如果你已经解决了该问题, 非常希望你能够分享一下解决方案, 写成博客, 将相关链接放在评论区, 以帮助更多的人 ^-^