题目描述
![在这里插入图片描述](https://img-blog.csdnimg.cn/b6fc6d0ff1fe45cbab9438def18113e2.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2Zpc2hlcmlzaA==,size_16,color_FFFFFF,t_70)
描述
有一个部门表departments简况如下:
![在这里插入图片描述](https://img-blog.csdnimg.cn/f534ebe3aa9b4a3682ae445b1eec7957.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2Zpc2hlcmlzaA==,size_16,color_FFFFFF,t_70)
有一个,部门员工关系表dept_emp简况如下:
![在这里插入图片描述](https://img-blog.csdnimg.cn/81e11823e1db42e0b21d170898654f0a.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2Zpc2hlcmlzaA==,size_16,color_FFFFFF,t_70)
有一个薪水表salaries简况如下:
![在这里插入图片描述](https://img-blog.csdnimg.cn/7d11d2ab545143fbb40a03835384a36b.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2Zpc2hlcmlzaA==,size_16,color_FFFFFF,t_70)
请你统计各个部门的工资记录数,给出部门编码dept_no、部门名称dept_name以及部门在salaries表里面有多少条记录sum,按照dept_no升序排序,以上例子输出如下:
示例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
题解
运行时间:13ms
超过80.58% 用Sqlite提交的代码
占用内存:3456KB
超过17.53%用Sqlite提交的代码
SELECT d1.dept_no, d1.dept_name, COUNT(d1.dept_no)
FROM departments AS d1 JOIN (
SELECT s.emp_no, d2.dept_no
FROM dept_emp AS d2 JOIN salaries AS s
ON d2.emp_no = s.emp_no
) AS t
ON d1.dept_no = t.dept_no
GROUP BY d1.dept_no
;