题解 | #统计各个部门的工资记录数#
统计各个部门的工资记录数
http://www.nowcoder.com/practice/6a62b6c0a7324350a6d9959fa7c21db3
先将部门员工关系表和薪水表联合,查出员工,薪水和部门的表
select s.emp_no,s.salary,d.dept_no from salaries s left join dept_emp d on s.emp_no=d.emp_no
之后进行联合,查出即可
select d.dept_no,d.dept_name,count(a.salary) as sum from departments d join ( select s.emp_no,s.salary,d.dept_no from salaries s left join dept_emp d on s.emp_no=d.emp_no ) as a on d.dept_no=a.dept_no group by d.dept_name order by d.dept_no
不要忘了要按dept_no升序排列