如何从ROOMS表统计7号、12号房间的人员、椅子及相关聚合指标

实现逻辑 #

按时间段（HSTART、HEND）分组，使用条件聚合将不同房间的指标转换为同一行的不同列，同时兼容空值、避免除零错误。

完整SQL语句 #

SELECT 
    HSTART,
    HEND,
    SUM(CASE WHEN ROOM_NO = 7 THEN PPL_Q ELSE 0 END) AS PPL_Q_ROOM7,
    SUM(CASE WHEN ROOM_NO = 12 THEN PPL_Q ELSE 0 END) AS PPL_Q_ROOM12,
    SUM(CASE WHEN ROOM_NO IN (7,12) THEN PPL_Q ELSE 0 END) AS PPL_Q_TOTAL,
    SUM(CASE WHEN ROOM_NO = 7 THEN CHAIR_Q ELSE 0 END) AS CHAIR_Q_ROOM7,
    SUM(CASE WHEN ROOM_NO = 12 THEN CHAIR_Q ELSE 0 END) AS CHAIR_Q_ROOM12,
    SUM(CASE WHEN ROOM_NO IN (7,12) THEN CHAIR_Q ELSE 0 END) AS CHAIR_Q_TOTAL,
    CASE WHEN SUM(CASE WHEN ROOM_NO IN (7,12) THEN CHAIR_Q ELSE 0 END) = 0 
         THEN 0 
         ELSE SUM(CASE WHEN ROOM_NO IN (7,12) THEN PPL_Q ELSE 0 END) * 1.0 / SUM(CASE WHEN ROOM_NO IN (7,12) THEN CHAIR_Q ELSE 0 END) 
    END AS PPL_PER_CHAIR
FROM ROOMS
WHERE ROOM_NO IN (7,12)
GROUP BY HSTART, HEND
ORDER BY HSTART, HEND;

原查询无返回结果排查方向 #

• 先执行基础排查语句确认是否有符合条件的记录：SELECT * FROM ROOMS WHERE ROOM_NO IN (7,12)，如果无返回说明库中不存在ROOM_NO为7或12的数据
• 检查ROOM_NO字段的数据类型，如果是字符型，需要将条件中的数值改为字符串格式，例如ROOM_NO IN ('7','12')

内容的提问来源于stack exchange，提问作者dwembe_xl