我有3张表--学生表、系表和考试结果表.我在这里try 做的是 Select 所有具有最高分数的部门名称/s,例如6.
我try 了以下查询,但在本例中,我们有2个6级的部门,但信息学部门有2个最高年级,而化学只有1个.我仍然在检索不应返回的化学系名称,而只返回化学系名称(此外,如果我们有2个最高年级的化学,我们应该能够检索信息学和化学记录).
SELECT department
FROM (SELECT d.department_name as department, count(e_r.grade) as cnt
FROM exam_results e_r
INNER JOIN students s ON e_r.student_id = s.student_id
INNER JOIN department d ON s.department_id = d.department_id
WHERE e_r.grade = 6
GROUP BY d.department_name
) as ex;
另外,通过下面的查询,我能够在WHERE子句中检索部门名称和给定‘n’级的计数,但由于某些原因,我无法实现我真正想要的结果.
SELECT department_name, max(cnt) as cnt
FROM (SELECT d.department_name as department_name, e_r.grade, count(e_r.grade) as cnt
FROM exam_results e_r
INNER JOIN students s ON e_r.student_id = s.student_id
INNER JOIN department d ON s.department_id = d.department_id
WHERE grade = 6
GROUP BY d.department_name, e_r.grade
) AS ex
GROUP BY department_name;
使用所解释的示例提供一个dbfiddle链接:https://dbfiddle.uk/siEjfNXP
将很高兴收到任何关于如何实现这一点的建议或提示,谢谢!
以下是带有示例值的表(全部包含在dbfiddle链接中):
学生桌:
student_id | department_id |
---|---|
1 | 1 |
2 | 1 |
3 | 2 |
4 | 2 |
5 | 3 |
6 | 3 |
7 | 4 |
8 | 4 |
9 | 5 |
10 | 5 |
部门表:
department_id | department_name |
---|---|
1 | Informatics |
2 | Biology |
3 | Physics |
4 | Geography |
5 | Chemistry |
EXAM_RESULTS表:
student_id | grade |
---|---|
1 | 6 |
2 | 6 |
3 | 4 |
4 | 4 |
5 | 3 |
6 | 3 |
7 | 2 |
8 | 2 |
9 | 6 |
10 | 5 |