统计各个部门的工资记录数

dept_no	dept_name
d001	Marketing
d002	Finance

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

emp_no	dept_no	from_date	to_date
10001	d001	2001-06-22	9999-01-01
10002	d001	1996-08-03	9999-01-01
10003	d002	1996-08-03	9999-01-01

有一个薪水表salaries简况如下:

emp_no	salary	from_date	to_date
10001	85097	2001-06-22	2002-06-22
10001	88958	2002-06-22	9999-01-01
10002	72527	1996-08-03	9999-01-01
10003	32323	1996-08-03	9999-01-01

请你统计各个部门的工资记录数，给出部门编码dept_no、部门名称dept_name以及部门在salaries表里面有多少条记录sum，按照dept_no升序排序，以上例子输出如下:

dept_no	dept_name	sum
d001	Marketing	3
d002	Finance	1

示例1

输入:

drop table if exists  `departments` ; 
drop table if exists  `dept_emp` ; 
drop table if exists  `salaries` ; 
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`));
INSERT INTO departments VALUES('d001','Marketing');
INSERT INTO departments VALUES('d002','Finance');
INSERT INTO dept_emp VALUES(10001,'d001','2001-06-22','9999-01-01');
INSERT INTO dept_emp VALUES(10002,'d001','1996-08-03','9999-01-01');
INSERT INTO dept_emp VALUES(10003,'d002','1996-08-03','9999-01-01');
INSERT INTO salaries VALUES(10001,85097,'2001-06-22','2002-06-22');
INSERT INTO salaries VALUES(10001,88958,'2002-06-22','9999-01-01');
INSERT INTO salaries VALUES(10002,72527,'1996-08-03','9999-01-01');
INSERT INTO salaries VALUES(10003,32323,'1996-08-03','9999-01-01');

输出:

d001|Marketing|3
d002|Finance|1

原站题解

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

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

select d.dept_no,d.dept_name,count(s.salary) sum from departments d inner join dept_emp e
on d.dept_no=e.dept_no
inner join salaries s 
on e.emp_no=s.emp_no
group by d.dept_no
order by d.dept_no

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

select t1.dept_no,t1.dept_name,count(t3.salary) sum 
from departments t1 join dept_emp t2 
on t1.dept_no=t2.dept_no
 join salaries t3 on t2.emp_no=t3.emp_no
group by t1.dept_no

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

select a.dept_no,a.dept_name,count(*) sum 
from departments a join dept_emp b
on a.dept_no=b.dept_no 
join salaries c
on b.emp_no=c.emp_no group by a.dept_no;

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

select d.dept_no,de.dept_name,count(s.salary)as sum
from dept_emp as d
left join   departments as de on de.dept_no=d.dept_no
left join  salaries as s on s.emp_no=d.emp_no
group by d.dept_no
order by d.dept_no

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

SELECT DE.dept_no,DE.dept_name,COUNT(S.salary) sum
FROM (SELECT *
      FROM dept_emp E LEFT JOIN departments D
      ON D.dept_no = E.dept_no) AS DE
JOIN salaries S ON DE.emp_no=S.emp_no
GROUP BY DE.dept_no 
ORDER BY DE.dept_no

上一题

下一题

详情