【牛客SQL】SQL3 查找当前薪水详情以及部门编号dept_no

题目描述

有一个全部员工的薪水表salaries简况如下:

有一个各个部门的领导表dept_manager简况如下:

请你查找各个部门当前领导的薪水详情以及其对应部门编号dept_no，输出结果以salaries.emp_no升序排序，并且请注意输出结果里面dept_no列是最后一列，以上例子输出如下:

示例1
输入：
drop table if exists  `salaries` ; 
drop table if exists  `dept_manager` ; 
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`));
CREATE TABLE `dept_manager` (
`dept_no` char(4) NOT NULL,
`emp_no` int(11) NOT NULL,
`to_date` date NOT NULL,
PRIMARY KEY (`emp_no`,`dept_no`));
INSERT INTO dept_manager VALUES('d001',10002,'9999-01-01');
INSERT INTO dept_manager VALUES('d002',10006,'9999-01-01');
INSERT INTO dept_manager VALUES('d003',10005,'9999-01-01');
INSERT INTO dept_manager VALUES('d004',10004,'9999-01-01');
INSERT INTO salaries VALUES(10001,88958,'2002-06-22','9999-01-01');
INSERT INTO salaries VALUES(10002,72527,'2001-08-02','9999-01-01');
INSERT INTO salaries VALUES(10003,43311,'2001-12-01','9999-01-01');
INSERT INTO salaries VALUES(10004,74057,'2001-11-27','9999-01-01');
INSERT INTO salaries VALUES(10005,94692,'2001-09-09','9999-01-01');
INSERT INTO salaries VALUES(10006,43311,'2001-08-02','9999-01-01');
INSERT INTO salaries VALUES(10007,88070,'2002-02-07','9999-01-01');
复制
输出：
10002|72527|2001-08-02|9999-01-01|d001
10004|74057|2001-11-27|9999-01-01|d004
10005|94692|2001-09-09|9999-01-01|d003
10006|43311|2001-08-02|9999-01-01|d002

题解

主要考察表连接JOIN和表排序ORDER BY：

运行时间：13ms 超过85.06% 用Sqlite提交的代码 占用内存：3408KB 超过37.17%用Sqlite提交的代码

SELECT a.*, b.dept_no 
FROM salaries a JOIN dept_manager b
WHERE b.emp_no IN (SELECT a.emp_no FROM salaries)
ORDER BY a.emp_no ASC; 

另外：存在换部门或者离职的情况，所以dept_manager.to_date='9999-01-01’是必须的：

运行时间：11ms 超过99.43% 用Sqlite提交的代码 占用内存：3448KB 超过10.85%用Sqlite提交的代码

SELECT a.*, b.dept_no 
FROM salaries a JOIN dept_manager b
WHERE b.emp_no IN (SELECT a.emp_no FROM salaries)
AND a.to_date = '9999-01-01' 
AND b.to_date = '9999-01-01'
ORDER BY a.emp_no ASC; 

简化一下：

运行时间：13ms 超过85.06% 用Sqlite提交的代码 占用内存：4324KB 超过3.75%用Sqlite提交的代码

SELECT a.*, b.dept_no 
FROM salaries a JOIN dept_manager b
ON b.emp_no = a.emp_no
WHERE a.to_date = '9999-01-01' 
AND b.to_date = '9999-01-01'
ORDER BY a.emp_no ASC;