题解 | #统计各个部门的工资记录数#
统计各个部门的工资记录数
http://www.nowcoder.com/practice/6a62b6c0a7324350a6d9959fa7c21db3
1. 先求出来人员的记录数,并封装为t1 .
select emp_no emp_no,count(1) cnt from salaries
GROUP BY emp_no
2. 然后 关联t2,t3拿到对应的值. 本质是考察sum
GROUP BY emp_no
2. 然后 关联t2,t3拿到对应的值. 本质是考察sum
select
t2.dept_no ,
t3.dept_name,
sum(t1.cnt) sum
from
dept_emp t2 ,
(
select emp_no emp_no,count(1) cnt from salaries
GROUP BY emp_no ) t1 ,
departments t3
where t1.emp_no = t2.emp_no
and t2.dept_no = t3.dept_no
group by t2.dept_no
order by t2.dept_no asc
t2.dept_no ,
t3.dept_name,
sum(t1.cnt) sum
from
dept_emp t2 ,
(
select emp_no emp_no,count(1) cnt from salaries
GROUP BY emp_no ) t1 ,
departments t3
where t1.emp_no = t2.emp_no
and t2.dept_no = t3.dept_no
group by t2.dept_no
order by t2.dept_no asc
投递美团等公司8个岗位
