题目
一所大学有 2 个数据表，分别是 student 和 department ，这两个表保存着每个专业的学生数据和院系数据。
写一个查询语句，查询 department 表中每个专业的学生人数 （即使没有学生的专业也需列出）。
将你的查询结果按照学生人数降序排列。 如果有两个或两个以上专业有相同的学生数目，将这些部门按照部门名字的字典序从小到大排列。
student 表格如下：
| Column Name | Type |
|---|---|
| student_id | Integer |
| student_name | String |
| gender | Character |
| dept_id | Integer |
其中， student_id 是学生的学号， student_name 是学生的姓名， gender 是学生的性别， dept_id 是学生所属专业的专业编号。
department 表格如下：
| Column Name | Type |
|---|---|
| dept_id | Integer |
| dept_name | String |
dept_id 是专业编号， dept_name 是专业名字。
这里是一个示例输入：
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 |
生成数据
DROP TABLE student
CREATE TABLE student
(
student_id INT,
student_name VARCHAR(10),
gender VARCHAR(2),
dept_id INT
)
INSERT INTO student VALUES(1,'Jack','M',1);
INSERT INTO student VALUES(2,'Jane','F',1);
INSERT INTO student VALUES(3,'Mark','M',2);
SELECT * FROM student
CREATE TABLE department3
(
dept_id INT,
dept_name VARCHAR(25)
)
INSERT INTO department3 VALUES(1,'Engineering');
INSERT INTO department3 VALUES(2,'Science');
INSERT INTO department3 VALUES(3,'Law');
SELECT * FROM department3;
SELECT S.`dept_id`, COUNT(S.`dept_id`)
FROM student AS S
GROUP BY S.`dept_id`;
SELECT dep.`dept_name`, IFNULL(tmp.cnt,0) AS `student_number`
FROM department3 AS dep
LEFT JOIN (SELECT S.`dept_id`, COUNT(S.`dept_id`) AS cnt
FROM student AS S
GROUP BY S.`dept_id`) AS tmp
ON dep.`dept_id` = tmp.`dept_id`
ORDER BY `student_number` DESC, dep.dept_name;
解答
先分组统计数量 再外连接 需要注意的是NULL转为0
SELECT dep.`dept_name`, IFNULL(tmp.cnt,0) AS `student_number`
FROM department3 AS dep
LEFT JOIN (SELECT S.`dept_id`, COUNT(S.`dept_id`) AS cnt
FROM student AS S
GROUP BY S.`dept_id`) AS tmp
ON dep.`dept_id` = tmp.`dept_id`
ORDER BY `student_number` DESC, dep.dept_name;
先外连接 再统计数量
SELECT dep.`dept_name`, COUNT(S.`student_id`) AS `student_number`
FROM department3 AS dep
LEFT JOIN student AS S
ON (dep.dept_id = S.dept_id)
GROUP BY dep.`dept_name`
ORDER BY `student_number` DESC, dep.dept_name
网友评论