【LeetCode-SQL】580. 统计各专业学生人数

一、题目

表: Student

+--------------+---------+
| Column Name  | Type    |
+--------------+---------+
| student_id   | int     |
| student_name | varchar |
| gender       | varchar |
| dept_id      | int     |
+--------------+---------+
Student_id是该表的主键。
dept_id是Department表中dept_id的外键。
该表的每一行都表示学生的姓名、性别和所属系的id。

表: Department

+-------------+---------+
| Column Name | Type    |
+-------------+---------+
| dept_id     | int     |
| dept_name   | varchar |
+-------------+---------+
Dept_id是该表的主键。
该表的每一行包含一个部门的id和名称。

编写一个SQL查询，为 Department 表中的所有部门(甚至是没有当前学生的部门)报告各自的部门名称和每个部门的学生人数。

按 student_number 降序 返回结果表。如果是平局，则按 dept_name 的 字母顺序 排序。

查询结果格式如下所示。

示例 1:

输入: 
Student 表:
+------------+--------------+--------+---------+
| student_id | student_name | gender | dept_id |
+------------+--------------+--------+---------+
| 1          | Jack         | M      | 1       |
| 2          | Jane         | F      | 1       |
| 3          | Mark         | M      | 2       |
+------------+--------------+--------+---------+
Department 表:
+---------+-------------+
| dept_id | dept_name   |
+---------+-------------+
| 1       | Engineering |
| 2       | Science     |
| 3       | Law         |
+---------+-------------+
输出: 
+-------------+----------------+
| dept_name   | student_number |
+-------------+----------------+
| Engineering | 2              |
| Science     | 1              |
| Law         | 0              |
+-------------+----------------+

二、解决

1、left join

思路：

1、如果 student 左连 department 就会忽略掉没有人的专业，造成最后专业缺失，所以要用 department 左连 student。

2、count(*) 计算的是总行数，不会忽略 null，但是这里有些专业没有人，计算出来应该是 0，所以应该使用 count(student_id)

代码：

SELECT
	dept_name,
	count( student_id ) AS student_number 
FROM
	department
    LEFT JOIN student ON department.dept_id = student.dept_id 
GROUP BY
	dept_name 
ORDER BY
	student_number DESC, dept_name ASC
;

# result1：
+-------------+----------------+
| dept_name   | student_number |
+-------------+----------------+
| Engineering | 2              |
| Science     | 1              |
| Law         | 0              |
+-------------+----------------+

错误：

# wrong anwser:
select b.dept_name, a.student_number
from 
(
    select dept_id, count(distinct(student_id)) as student_number
    from student
    group by dept_id
) a 
right join department b on a.dept_id = b.dept_id;

# result2： 错误答案，有一个为null
+-------------+----------------+
| dept_name   | student_number |
+-------------+----------------+
| Engineering | 2              |
| Science     | 1              |
| Law         | null           |
+-------------+----------------+

补充 ：count(*) 和 count(1)和count(列名)区别

执行效果上：

• count(*)包括了所有的列，相当于行数，在统计结果的时候，不会忽略为NULL的值。
• count(1)包括了忽略所有列，用1代表代码行，在统计结果的时候，不会忽略为NULL的值。
• count(列名)只包括列名那一列，在统计结果的时候，会忽略列值为空（这里的空不是指空字符串或者0，而是表示null）的计数，即某个字段值为NULL时，不统计。

执行效率上：

• 列名为主键，count(列名)会比count(1)快
• 列名不为主键，count(1)会比count(列名)快
• 如果表多个列并且没有主键，则 count(1 的执行效率优于 count（*）
• 如果有主键，则 select count（主键）的执行效率是最优的
• 如果表只有一个字段，则 select count（*）最优。

三、参考

1、需要注意的两个考察点
2、统计各专业学生人数
3、执行count(1)、count(*) 与 count(列名) 到底有什么区别？