统计各个部门对应员工涨幅的次数总和

题目描述

统计各个部门对应员工涨幅的次数总和，给出部门编码dept_no、部门名称dept_name以及次数sum

CREATE TABLE `departments` (
`dept_no` char(4) NOT NULL,
`dept_name` varchar(40) NOT NULL,
PRIMARY KEY (`dept_no`));
CREATE TABLE `dept_emp` (
`emp_no` int(11) NOT NULL,
`dept_no` char(4) NOT NULL,
`from_date` date NOT NULL,
`to_date` date NOT NULL,
PRIMARY KEY (`emp_no`,`dept_no`));
CREATE TABLE `salaries` (
`emp_no` int(11) NOT NULL,
`salary` int(11) NOT NULL,
`from_date` date NOT NULL,
`to_date` date NOT NULL,
PRIMARY KEY (`emp_no`,`from_date`));

解题思路

本题的思路在于将部门表和薪资表关联起来后按照部门编号分组并求出涨幅次数，然后再关联部门信息表获取部门名称。

select ds.dept_no,ds.dept_name,dp.sum
from departments as ds,
     (select d.dept_no,count(*) as sum
        from dept_emp as d,salaries as s
        where d.emp_no = s.emp_no
        group by d.dept_no) as dp
where ds.dept_no = dp.dept_no