题目描述
![在这里插入图片描述](https://img-blog.csdnimg.cn/a67fcee837b64ce9b32b77137add9a29.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2Zpc2hlcmlzaA==,size_16,color_FFFFFF,t_70)
描述
有一个员工表employees简况如下:
![在这里插入图片描述](https://img-blog.csdnimg.cn/18b43a15667046f38adc841c21fb432d.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/b234d107081643339e62520948392bf0.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2Zpc2hlcmlzaA==,size_16,color_FFFFFF,t_70)
有一个部门经理表dept_manager简况如下:
![在这里插入图片描述](https://img-blog.csdnimg.cn/803fd57b6d19469b85e95fe7ea92920f.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2Zpc2hlcmlzaA==,size_16,color_FFFFFF,t_70)
有一个薪水表salaries简况如下:
![在这里插入图片描述](https://img-blog.csdnimg.cn/dc47a9f9e94b489c94919159c8fda542.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2Zpc2hlcmlzaA==,size_16,color_FFFFFF,t_70)
获取所有非manager员工薪水情况,给出dept_no、emp_no以及salary,以上例子输出:
![在这里插入图片描述](https://img-blog.csdnimg.cn/39f98ea5353545fc81d9a192b78a793c.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2Zpc2hlcmlzaA==,size_16,color_FFFFFF,t_70)
示例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;