牛客在线编程练习：SQL12_困难

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 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,'d001','1996-08-03','1997-08-03');

INSERT INTO salaries VALUES(10001,90000,'1986-06-26','1987-06-26');
INSERT INTO salaries VALUES(10001,88958,'2002-06-22','9999-01-01');
INSERT INTO salaries VALUES(10002,72527,'1996-08-03','1997-08-03');
INSERT INTO salaries VALUES(10002,72527,'2000-08-02','2001-08-02');
INSERT INTO salaries VALUES(10002,72527,'2001-08-02','9999-01-01');
INSERT INTO salaries VALUES(10003,90000,'1996-08-03','1997-08-03');

题解

# 方式1：内连接+子查询
SELECT D1.dept_no,D1.emp_no,S1.salary
FROM dept_emp D1
INNER JOIN salaries S1 ON D1.emp_no = S1.emp_no AND D1.to_date = '9999-01-01' AND S1.to_date='9999-01-01' # 显式内连接，选出两个“当前”。连接条件可以是多个
WHERE S1.salary = (
    SELECT MAX(S2.salary)
    FROM salaries S2
    INNER JOIN dept_emp D2 ON D2.emp_no = S2.emp_no AND D2.to_date = '9999-01-01' AND S2.to_date='9999-01-01'
    WHERE D2.dept_no = D1.dept_no
)
ORDER BY D1.dept_no
## 关于语句【WHERE D2.dept_no = D1.dept_no】，不清楚为什么要加，不加牛客测试不通过。但是按照题目自己建库并插入数据后测试，不加的结果是一样的。想不懂这里为什么要使用自连接。
## 本题解题方式较多，思路都是先找到MAX(salary)，后查询对应的员工与部门。

个人整理的笔记，仅供学习使用，有问题麻烦指正。
题目及解答思路均来源于：牛客网