Leetcode_580_统计各专业学生人数_SQL

# Write your MySQL query statement below
select d.dept_name, count(s.student_id) as student_number
from department as d
left join student as s 
on d.dept_id = s.dept_id
group by d.dept_id
order by student_number desc

如果直接写的话，很绕的一道题，个人比较喜欢用下面的写法，感觉更加清晰直观

with t as (
    select dept_id, count(*) as student_number
    from student
    group by dept_id
)

select d.dept_name, ifnull(t.student_number, 0) as student_number
from department as d left join t
on d.dept_id = t.dept_id
order by t.student_number desc