我得到的数据库具有以下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


我希望这有帮助!!祝好运!

10-07 16:52
查看更多