如何用单条SQL实现教室编码查询及2014年10月授课总数统计？

问题背景 #

求解SQL习题时需要通过单条查询同时实现两个核心需求：

• 统计符合筛选条件的教室在2014年10月的授课总数量
• 查询对应符合条件的教室编码
  相关数据库表结构、习题要求参考题目附图，因平台限制无法直接上传图片。

原有错误实现 #

已编写的存在逻辑错误的SQL代码如下：

SELECT L0.RoomID,
       COUNT(*),
       MAX(AttendingStudent#)
FROM   LECTURE L0
WHERE  L0.RoomID IN(
  SELECT L1.RoomID
  FROM   CLASSROOM C, LECTURE L1
  WHERE  C.RoomID = L1.RoomID 
  AND C.Video_Kit = 'yes' 
  AND L1.Date >= 01/01/2014 
  AND Date <= 31/12/2014
  GROUP BY L1.RoomID
  HAVING COUNT(DISTINCT L1.CCode) >= 20
)
AND Date >= 01/10/2014 
AND Date <= 31/10/2014
GROUP BY L0.RoomID, L0.Date, L0.StartHour

原有代码问题点 #

• 分组粒度过细：外层按RoomID, Date, StartHour分组，统计结果是单教室单日单时段的授课数，无法得到整个10月维度的单教室总授课量
• 日期写法错误：直接书写01/01/2014会被SQL引擎识别为数值除法运算，不会解析为日期值
• 字段存在歧义：子查询中Date字段未绑定表别名，容易出现字段匹配错误
• 隐式连接可读性差：子查询中用逗号分隔表的旧写法容易漏写关联条件，产生笛卡尔积错误

正确实现方案 #

SELECT 
    L.RoomID AS 教室编码,
    COUNT(*) AS 2014年10月授课总数量,
    MAX(L.AttendingStudent#) AS 单课程最大参会学生数
FROM LECTURE L
INNER JOIN CLASSROOM C 
    ON L.RoomID = C.RoomID
WHERE C.Video_Kit = 'yes'
  AND L.RoomID IN (
      SELECT L1.RoomID
      FROM LECTURE L1
      WHERE L1.Date BETWEEN STR_TO_DATE('2014-01-01', '%Y-%m-%d') 
                        AND STR_TO_DATE('2014-12-31', '%Y-%m-%d')
      GROUP BY L1.RoomID
      HAVING COUNT(DISTINCT L1.CCode) >= 20
  )
  AND L.Date BETWEEN STR_TO_DATE('2014-10-01', '%Y-%m-%d') 
                 AND STR_TO_DATE('2014-10-31', '%Y-%m-%d')
GROUP BY L.RoomID;

代码逻辑说明 #

• 用显式INNER JOIN关联教室表和授课表，直接筛选配备视频设备的教室
• 子查询独立筛选2014年全年开设不同课程数≥20的符合资质教室
• 外层过滤2014年10月的授课记录，按教室维度分组聚合，直接得到每个符合条件教室的10月总授课量
• 所有日期值用标准日期函数转换，避免类型解析错误；所有字段绑定表别名，避免字段歧义

注：如果使用SQL Server数据库，可将STR_TO_DATE('2014-xx-xx', '%Y-%m-%d')替换为CAST('2014-xx-xx' AS DATE)即可兼容。

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