【牛客SQL】SQL26 汇总各个部门当前员工的title类型的分配数目

题目描述

描述
有一个部门表departments简况如下:

有一个，部门员工关系表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');
复制
输出：
d001|Marketing|Senior Engineer|1
d001|Marketing|Staff|1
d002|Finance|Senior Engineer|1

题解

这里的GROUP BY 对象可能很难想到，但是确实就是dept.dept_no, t.title都要GROUP BY

运行时间：15ms
超过64.93% 用Sqlite提交的代码
占用内存：3584KB
超过7.38%用Sqlite提交的代码

SELECT dept.dept_no, depa.dept_name, t.title, COUNT(t.title) AS count
FROM dept_emp AS dept, departments AS depa, titles AS t
ON dept.dept_no = depa.dept_no
AND dept.emp_no = t.emp_no
GROUP BY dept.dept_no, t.title
ORDER BY dept.dept_no ASC
;