我正在努力尋找這個練習的解決方案。我真的不明白如何在一個查詢中用“選擇教室的代碼”來實作“2014年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
uj5u.com熱心網友回復：
您擁有大部分零件，但您只需要以正確的方式將它們組合在一起。
如果您將問題分解為多個部分，可能會更容易思考：
對于有視頻套件的教室......選擇教室的代碼
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
)
選擇... 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 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
)
現在，有更有效的方法可以使用連接條件撰寫最后兩部分，但是，由于這是一個家庭作業問題，我將讓您探索并提出您自己更好的解決方案。
轉載請註明出處，本文鏈接：https://www.uj5u.com/gongcheng/489467.html