有一个,部门员工关系表dept_emp简况如下:
有一个职称表titles简况如下:
汇总各个部门当前员工的title类型的分配数目,即结果给出部门编号dept_no、dept_name、其部门下所有的员工的title以及该类型title对应的数目count,结果按照dept_no升序排序
示例1
drop table if exists `departments` ;
drop table if exists `dept_emp` ;
drop table if exists titles ;
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 titles (
`emp_no` int(11) NOT NULL,
`title` varchar(50) NOT NULL,
`from_date` date NOT NULL,
`to_date` date DEFAULT NULL);
INSERT INTO departments VALUES('d001','Marketing');
INSERT INTO departments VALUES('d002','Finance');
INSERT INTO dept_emp VALUES(10001,'d001','1986-06-26','9999-01-01');
INSERT INTO dept_emp VALUES(10002,'d001','1996-08-03','9999-01-01');
INSERT INTO dept_emp VALUES(10003,'d002','1995-12-03','9999-01-01');
INSERT INTO titles VALUES(10001,'Senior Engineer','1986-06-26','9999-01-01');
INSERT INTO titles VALUES(10002,'Staff','1996-08-03','9999-01-01');
INSERT INTO titles VALUES(10003,'Senior Engineer','1995-12-03','9999-01-01');
分析:
统计人数需要两个分组,一个部门分组,一个职称分组。
最终结果按照部门-职称显示人数,所以把部门和职称作为聚合键。
select tab.dept_no,dp.dept_name,tab.title,tab.cnt
from(
select
dpem.dept_no,
ti.title,
count() cnt
from dept_emp dpem
join titles ti
on dpem.emp_no = ti.emp_no
and dpem.to_date = '9999-01-01'
and ti.to_date = '9999-01-01'
group by dpem.dept_no,ti.title
)tab
join departments dp
where dp.dept_no = tab.dept_no
order by dp.dept_no asc
提交结果:答案正确 运行时间:21ms 占用内存:3448KB 使用语言:Sqlite 用例通过率:100.00%