题解 | #汇总各个部门当前员工的title类型的分配数目#
汇总各个部门当前员工的title类型的分配数目
http://www.nowcoder.com/practice/4bcb6a7d3e39423291d2f7bdbbff87f8
本题知识点:
-
group by a,b是将a与b合在一起进行分组
-
order by a,b是按照a排序如果a相同再按照b排序 首先按照部门以及title进行分组,然后join部门表,最后进行排序,代码如下:
select
b.dept_no as dept_no,
a.dept_name as dept_name,
b.title as title,
b.cnt as ` count `
from
departments a
inner join (
select
a.dept_no as dept_no,
c.title as title,
count(*) as cnt
from
(
departments a
inner join dept_emp b on a.dept_no = b.dept_no
inner join titles c on b.emp_no = c.emp_no
)
where
b.to_date = '9999-01-01'
and c.to_date = '9999-01-01'
group by
a.dept_no,
c.title
) as b on a.dept_no = b.dept_no
order by
b.dept_no,
b.title;