SQL 嵌套聚合函数

Question

我有表:

Student(sID, firstName, lastName, email, cgpa)
Course(cNum, name, dept, credit)
Offering(oID, cNum, dept, year, term, instructor)
Took(sID, oID, grade)

我正在尝试完成问题:

Find all courses for the term 2017F and the current enrolment

我目前有这个查询来获取每门类(class)的注册学生人数:

SELECT Took.oID, COUNT(*) AS enrolment
FROM Took
GROUP BY Took.oID
HAVING COUNT(*) > 0

嵌套在此语句中以获取我希望注册计数的正确类(class):

SELECT oID
FROM Offering
WHERE Offering.year = 2017
AND Offering.term = 'F'

两者都嵌套在此查询中以将所有内容联系在一起:

SELECT DISTINCT Offering.cNum, Course.name, (I WOULD LIKE COUNT(*) AS enrolment HERE)
FROM Offering NATURAL JOIN Course
WHERE Offering.oID IN (
            SELECT oID
            FROM Offering
            WHERE Offering.year = 2017
            AND Offering.term = 'F'
            AND oID IN (
                    SELECT Took.oID, COUNT(*) AS enrolment
                    FROM Took
                    GROUP BY Took.oID
                    HAVING COUNT(*) > 0))
GROUP BY Offering.cNum, Course.name;

我的问题是，如何将生成的 COUNT(*) AS 注册从最远的嵌套查询传递到初始查询，以便它可以显示在生成的投影中？ (这是作业)

Answer 1

如果我理解正确，您可以尝试在 from 中使用子查询和 JOIN 而不是 where 子查询。

然后你可以从子查询中得到count列。

SELECT DISTINCT Offering.cNum, Course.name,t1.enrolment
FROM Offering 
JOIN (
    SELECT Took.oID, 
        COUNT(*) AS enrolment
    FROM Took
    GROUP BY Took.oID
    HAVING COUNT(*) > 0
) t1 on t1.oID = Offering.oID
NATURAL JOIN Course
WHERE Offering.year = 2017 AND Offering.term = 'F'