我得到的数据库具有以下3个表:
instructor(ID, name, dept_name, salary)
teaches(ID, course_id, sec_id, semester, year)
takes(ID, course_id, sec_id, semester, year, grade)
该查询的目的是计算每位老师教的学生总数,但每位学生每位老师只能计算一次。我的问题是现在教零学生的老师没有被包括在内。
我想要的示例输出是这样的:
dept_name id ins_name students_taught
Accounting 79081 Ullman 1218
Accounting 14365 Lembr 529
Accounting 77645 Jarold 0
Astronomy 43779 Romero 981
Athletics 41930 Tung 749
Athletics 63287 Jaekel 524
Athletics 15347 Bawa 266
Athletics 76458 Smith 0
Biology 81991 Valtchev 550
Biology 80759 Queiroz 312
Biology 98738 Quincy 0
Biology 22213 Lee 0
Comp. Sci. 34175 Bondi 817
Comp. Sci. 3335 Bourrier 566
但是问题是我得到了这个:
dept_name id name students_taught
Accounting 79081 Ullman 1218
Accounting 14365 Lembr 529
Astronomy 43779 Romero 981
Athletics 41930 Tung 749
Athletics 63287 Jaekel 524
Athletics 15347 Bawa 266
Biology 81991 Valtchev 550
Biology 80759 Queiroz 312
Comp. Sci. 34175 Bondi 817
Comp. Sci. 3335 Bourrier 566
我尝试了许多不同的方法来执行此操作,例如使用where子句或使用左联接,但结果始终相同。我有什么想念的吗?
供参考,这是我针对此问题进行的两种不同尝试:
Attempt 1:
SELECT instructor.dept_name AS dept_name,
instructor.id AS inID,
instructor.name AS name,
count(DISTINCT takes.id) AS total
FROM instructor
LEFT OUTER JOIN teaches ON instructor.id = teaches.id
JOIN
(SELECT dept_name,
id,
name
FROM instructor) AS ins,
takes
WHERE takes.course_id = teaches.course_id
AND takes.sec_id = teaches.sec_id
GROUP BY inID
ORDER BY dept_name,
total DESC;
Attempt 2:
SELECT instructor.dept_name AS dept_name,
instructor.id,
instructor.name,
count(DISTINCT takes.id) AS total
FROM instructor
INNER JOIN teaches ON teaches.id = instructor.id
INNER JOIN takes ON takes.course_id = teaches.course_id
AND takes.sec_id = teaches.sec_id
GROUP BY ins.id
ORDER BY dept_name,
total DESC;
最佳答案
请注意原始帖子中的@GordonLinoff评论。我假设Teaches.ID = Instructor.ID,如尝试#2所示。如果我假设做错了,则没有任何关系,因此您无法获得想要的结果。
您的第二次尝试非常接近。陈述中的错误
SELECT a.dept_name
,a.ID
,a.NAME
,count(DISTINCT c.ID) AS Total
FROM instructor a
LEFT JOIN teaches b ON b.ID = a.ID
LEFT JOIN takes c ON c.course_id = b.course_id
AND c.sec_id = b.sec_id
GROUP BY a.dept_name
,a.ID
,a.NAME
我使用一些示例数据对其进行了测试,并且效果很好。
样本数据
这是原始数据。请注意,一位教授如何让一名学生参加一个以上的班级。
还要注意,我有一个学生在同一堂课上列出两次以进行测试。
ID name dept_name salary ID course_id sec_id semester year ID course_id sec_id semester year grade
79081 Ullman Accounting 100 79081 1 2 1 2019 13 1 2 1 2019 12
14365 Lembr Accounting 100 14365 2 2 1 2019 14 2 2 1 2019 12
14365 Lembr Accounting 100 14365 2 2 1 2019 14 2 2 1 2019 12
77645 Jarold Accounting 100 77645 3 2 1 2019 10 3 2 1 2019 12
77645 Jarold Accounting 100 77645 3 2 1 2019 11 3 2 1 2019 12
77645 Jarold Accounting 100 77645 3 2 1 2019 12 3 2 1 2019 12
77645 Jarold Accounting 100 77645 4 2 1 2019 10 4 2 1 2019 12
这是结果
dept_name ID NAME Total
Accounting 14365 Lembr 1
Accounting 77645 Jarold 3
Accounting 79081 Ullman 1
我希望这有帮助!!祝好运!