MySQL 部门名称和学生人数
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/42773730/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Department name and number of students
提问by SolakiR
I found one question in MySQL I am trying. Please tell me if following solution will work or is there any better solution?
我在 MySQL 中发现了一个我正在尝试的问题。请告诉我以下解决方案是否有效，或者是否有更好的解决方案？
select D.DEPT_NAME, COUNT(*)
from Departments D
left outer join STUDENTS S
on S.Dept_ID = D.Dept_ID
group by D.DEPT_NAME
order by 2 desc, 1
Students table has following fields:
学生表具有以下字段：
Student_ID
Student_Name
Gender
Dept_ID
Departments table has following fields:
部门表具有以下字段：
Dept_ID
Dept_Name
A university uses 2 data tables, Students and Departments, to store data about its students and the departments associated with each major.
一所大学使用 2 个数据表“学生”和“系”来存储有关其学生和与每个专业相关的系的数据。
Write a query to print the respective department name and number of students majoring in each department for all departments in the Departments table (even ones with no current students).
Sort your results by descending number of students; if two or more departments have same number of students, then sort those departments alphabetically by department name.
编写一个查询，在 Departments 表中打印所有部门的相应部门名称和每个部门主修的学生人数（即使是没有当前学生的部门）。
按学生人数降序对结果进行排序；如果两个或多个部门的学生人数相同，则按部门名称的字母顺序对这些部门进行排序。
回答by Harsh Makani
Forgive me altering the formatting of the code.
请原谅我改变了代码的格式。
I would change the ORDER BY, as follows:
我会更改 ORDER BY，如下所示：
SELECT
d.DEPT_NAME,
COUNT(s.STUDENT_ID)
FROM
Departments d
LEFT JOIN Students s ON d.DEPT_ID = s.DEPT_ID
GROUP by
d.DEPT_ID
ORDER by
COUNT(s.STUDENT_ID) DESC,
d.DEPT_NAME ASC
回答by O. Jones
You need a way to count the students in each department, then you need a way to list alldepartments, even those without students.
您需要一种方法来计算每个部门的学生人数，然后您需要一种方法来列出所有部门，甚至是那些没有学生的部门。
Counting the students in each department: (http://sqlfiddle.com/#!15/39a8b/15/0)
统计各系的学生人数：（http://sqlfiddle.com/#!15/39a8b/15/0）
SELECT Dept_ID, COUNT(*) Students
FROM STUDENTS
GROUP BY Dept_ID
Then, treating that as a subquery, left join it to your other table. (http://sqlfiddle.com/#!15/39a8b/16/0)
然后，将其视为子查询，将其加入另一个表。( http://sqlfiddle.com/#!15/39a8b/16/0)
SELECT D.DEPT_NAME, S.Students
FROM Departments D
LEFT JOIN (
SELECT Dept_ID, COUNT(*) Students
FROM STUDENTS
GROUP BY Dept_ID
) S ON D.Dept_ID = S.Dept_ID
The LEFT JOIN preserves rows in the DEPARTMENTS table that don't match the ONclause. This gets you stuff like this.
LEFT JOIN 保留 DEPARTMENTS 表中与ON子句不匹配的行。这会让你得到这样的东西。
Biology 7
Mathematics (NULL)
Sociology 11
Physics 3
So you have to deal with that (NULL) problem. Here's how. Change the SELECT to say
所以你必须处理那个（NULL）问题。就是这样。改变 SELECT 说
SELECT D.DEPT_NAME, IFNULL(S.Students,0)
It's a little tricky to join a table to an aggregate where the aggregate (the COUNT/GROUP BY query) has missing data. But that's how you do it.
将表连接到聚合（COUNT/GROUP BY 查询）缺少数据的聚合有点棘手。但你就是这样做的。
You can figure out the ORDER BYstuff on your own.
你可以ORDER BY自己弄清楚这些东西。
回答by Alok Saxena
Excute below.
下面执行。
SELECT
ad.Dept_Name,
count(ass.Student_Id) as Stduent_Enrolled
FROM [Alok.Departments] ad
Left Outer Join [Alok.Students] ass
ON ad.Dept_ID = ass.Dept_ID
Group by ad.Dept_Name
ORDER by
CASE WHEN COUNT(ad.Dept_ID) >=2
THEN ad.DEPT_NAME END desc,
CASE WHEN COUNT(ad.Dept_ID) < 2
THEN ad.DEPT_NAME END asc
