汇总各个部门当前员工的title类型的分配数目

dept_no	dept_name
d001	Marketing
d002	Finance

有一个，部门员工关系表dept_emp简况如下:

emp_no	dept_no	from_date	to_date
10001	d001	1986-06-26	9999-01-01
10002	d001	1996-08-03	9999-01-01
10003	d002	1995-12-03	9999-01-01

有一个职称表titles简况如下:

emp_no	title	form_date	to_date
10001	Senior Engineer	1986-06-26	9999-01-01
10002	Staff	1996-08-03	9999-01-01
10003	Senior Engineer	1995-12-03	9999-01-01

汇总各个部门当前员工的title类型的分配数目，即结果给出部门编号dept_no、dept_name、其部门下所有的员工的title以及该类型title对应的数目count，结果按照dept_no升序排序，dept_no一样的再按title升序排序

dept_no	dept_name	title	count
d001	Marketing	Senior Engineer	1
d001	Marketing	Staff	1
d002	Finance	Senior Engineer	1

示例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');

输出:

d001|Marketing|Senior Engineer|1
d001|Marketing|Staff|1
d002|Finance|Senior Engineer|1

原站题解

上次编辑到这里，代码来自缓存点击恢复默认模板

Sqlite 解法, 执行用时: 10ms, 内存消耗: 3300KB, 提交时间: 2020-11-22

select a.dept_no,c.dept_name,title,count(a.emp_no) as count
from dept_emp as a 
join titles as b  on a.emp_no=b.emp_no
join departments c on a.dept_no=c.dept_no
where a.to_date='9999-01-01'
and b.to_date='9999-01-01'
group by a.dept_no,c.dept_name,title
order by a.dept_no

Sqlite 解法, 执行用时: 10ms, 内存消耗: 3376KB, 提交时间: 2021-09-05

select
    t2.dept_no
    ,t3.dept_name
    ,t1.title
    ,count(*) cnt
from titles t1
inner join dept_emp t2
on t1.emp_no=t2.emp_no
left join departments t3
on t2.dept_no=t3.dept_no
group by t2.dept_no,t3.dept_name,t1.title

Sqlite 解法, 执行用时: 10ms, 内存消耗: 3376KB, 提交时间: 2021-08-04

select de.dept_no,de.dept_name,t.title,count(*)

from departments de,dept_emp d,titles t

where d.dept_no=de.dept_no
and d.emp_no=t.emp_no

group by de.dept_no,title
order by de.dept_no;

Sqlite 解法, 执行用时: 10ms, 内存消耗: 3448KB, 提交时间: 2021-06-07

select d.dept_no,de.dept_name,t.title,count(d.emp_no)as count
from dept_emp as d 
inner join departments as de on de.dept_no=d.dept_no
inner join titles as t on t.emp_no=d.emp_no
group by d.dept_no,de.dept_name,title
order by d.dept_no

Sqlite 解法, 执行用时: 10ms, 内存消耗: 3460KB, 提交时间: 2021-07-31

select de.dept_no, d.dept_name,t.title,count(*)as count 
from titles t 
left join dept_emp de 
on t.emp_no = de.emp_no
left join departments d 
on de.dept_no = d.dept_no
group by de.dept_no,t.title

上一题

下一题

详情