如何只用一个 SQL 查询来解决这个练习

发布于06月10日

我正在努力找到这个练习的解决方案.

很遗憾，我无法直接附上我的图片，很抱歉我可能会出现链接和其他错误.

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

推荐答案

您拥有大部分部件，但只需以正确的方式将它们组合在一起即可.

如果您将问题分解为多个部分，可能更容易思考:

对于有视频工具包的教室... Select 教室的代码

SELECT roomid
FROM   classroom
WHERE  video_kit = 'yes'

2014年全年至少举办了20门不同的课程

SELECT roomid
FROM   classroom
WHERE  video_kit = 'yes'
AND    roomid IN (
         SELECT roomid
         FROM   lecture
         WHERE  "Date" >= DATE '2014-01-01'
         AND    "Date" <  DATE '2015-01-01'
         GROUP BY roomid
         HAVING COUNT(DISTINCT ccode) >= 20
       )

Select 2014年10月提供的讲座总数

SELECT roomid,
       ( SELECT COUNT(*)
         FROM   lecture l
         WHERE  c.roomid = l.roomid
         AND    "Date" >= DATE '2014-10-01'
         AND    "Date" <  DATE '2014-11-01'
       ) AS num_lectures
FROM   classroom c
WHERE  video_kit = 'yes'
AND    roomid IN (
         SELECT roomid
         FROM   lecture
         WHERE  "Date" >= DATE '2014-01-01'
         AND    "Date" <  DATE '2015-01-01'
         GROUP BY roomid
         HAVING COUNT(DISTINCT ccode) >= 20
       )

Select 参加此类讲座的最大学生人数.

SELECT roomid,
       ( SELECT COUNT(*)
         FROM   lecture l
         WHERE  c.roomid = l.roomid
         AND    "Date" >= DATE '2014-10-01'
         AND    "Date" <  DATE '2014-11-01'
       ) AS num_lectures,
       ( SELECT MAX(AttendingStudent#)
         FROM   lecture l
         WHERE  c.roomid = l.roomid
         AND    "Date" >= DATE '2014-10-01'
         AND    "Date" <  DATE '2014-11-01'
       ) AS max_attending_students
FROM   classroom c
WHERE  video_kit = 'yes'
AND    roomid IN (
         SELECT roomid
         FROM   lecture
         WHERE  "Date" >= DATE '2014-01-01'
         AND    "Date" <  DATE '2015-01-01'
         GROUP BY roomid
         HAVING COUNT(DISTINCT ccode) >= 20
       )

现在，有更有效的方法使用连接条件来编写最后两部分，但是，由于这是一个家庭作业(job)问题，我将让您探索这一点，并提出自己更好的解决方案.