SQL练习00003

一所大学有 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

--建表语句:
CREATE TABLE IF NOT EXISTS student (student_id INT,student_name VARCHAR(45), gender VARCHAR(6), dept_id INT)
CREATE TABLE IF NOT EXISTS department (dept_id INT, dept_name VARCHAR(255))
Truncate table student
insert into student (student_id, student_name, gender, dept_id) values ('1', 'Jack', 'M', '1')
insert into student (student_id, student_name, gender, dept_id) values ('2', 'Jane', 'F', '1')
insert into student (student_id, student_name, gender, dept_id) values ('3', 'Mark', 'M', '2')
Truncate table department
insert into department (dept_id, dept_name) values ('1', 'Engineering')
insert into department (dept_id, dept_name) values ('2', 'Science')
insert into department (dept_id, dept_name) values ('3', 'Law')

--答案1
--自己把问题复杂化了,count(字段)就可以了
select
  *
from
  (select
    dept_name,
    sum(mark) as student_number
  from
    (select
      d.dept_name as dept_name,
      if(s.dept_id is null,0,1) as mark
    from
      student s right join
      department d on 
      s.dept_id=d.dept_id) a
    group by dept_name) b order by student_number desc

--答案2
select
  d.dept_name as dept_name,
  count(s.dept_id) as student_number
from
  student s right join
  department d on 
  s.dept_id=d.dept_id group by
  s.dept_id order by count(s.dept_id) desc

SELECT
  d.dept_name as dept_name,
  count(s.dept_id) as student_number
FROM
  department d LEFT JOIN
  student s ON
  d.dept_id=s.dept_id group by 
  s.dept_id ORDER BY 
  count(s.dept_id) desc,d.dept_name