【牛客SQL】SQL24 获取所有非manager员工当前的薪水情况

题目描述

描述 有一个员工表employees简况如下:

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

有一个部门经理表dept_manager简况如下:

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

获取所有非manager员工薪水情况，给出dept_no、emp_no以及salary，以上例子输出:

示例1
输入：
drop table if exists  `dept_emp` ; 
drop table if exists  `dept_manager` ; 
drop table if exists  `employees` ; 
drop table if exists  `salaries` ; 
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 `dept_manager` (
`dept_no` char(4) NOT NULL,
`emp_no` int(11) NOT NULL,
`from_date` date NOT NULL,
`to_date` date NOT NULL,
PRIMARY KEY (`emp_no`,`dept_no`));
CREATE TABLE `employees` (
`emp_no` int(11) NOT NULL,
`birth_date` date NOT NULL,
`first_name` varchar(14) NOT NULL,
`last_name` varchar(16) NOT NULL,
`gender` char(1) NOT NULL,
`hire_date` date NOT NULL,
PRIMARY KEY (`emp_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 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_manager VALUES('d001',10002,'1996-08-03','9999-01-01');
INSERT INTO employees VALUES(10001,'1953-09-02','Georgi','Facello','M','1986-06-26');
INSERT INTO employees VALUES(10002,'1964-06-02','Bezalel','Simmel','F','1996-08-03');
INSERT INTO salaries VALUES(10001,88958,'1986-06-26','9999-01-01');
INSERT INTO salaries VALUES(10002,72527,'1996-08-03','9999-01-01');
复制
输出：
d001|10001|88958

题解

JOIN加子查询

运行时间：14ms 超过76.03% 用Sqlite提交的代码 占用内存：3456KB 超过27.00%用Sqlite提交的代码

SELECT t.dept_no, em.emp_no, t.salary FROM employees AS em
JOIN (
    SELECT de.emp_no, de.dept_no, sa.salary
    FROM dept_emp de JOIN salaries sa
    ON de.emp_no = sa.emp_no
    AND de.to_date = '9999-01-01'
) AS t
WHERE em.emp_no NOT IN (
    SELECT emp_no FROM dept_manager
)
AND t.emp_no = em.emp_no;