牛客网：获取所有部门中员工薪水最高的相关信息SQL解题思路

先上题目：
有一个员工表dept_emp简况如下:

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

获取所有部门中员工薪水最高的相关信息，给出dept_no, emp_no以及其对应的salary，按照部门编号升序排列，以上例子输出如下:

解题思路：使用窗口函数之排序函数

                           * 窗口函数之排序函数基础知识 *

• rank():跳跃式排序——比如分数为99，99，90，89，那么通过这个函数得到的排名为1，1，3，4
• dense_rank():并列连续型排序——比如分数为99，99，90，89，那么通过这个函数得到的排名为1，1，2，3
• row_number():连续型排序——比如分数为99，99，90，89，那么通过这个函数得到的排名为1，2，3，

了解了窗口函数的基础知识后。再了解窗口函数的语法：

window_function_name(expression) 
    OVER (
        [partition_defintion]
        [order_definition]
        [frame_definition]
    )

当你熟悉了窗口函数后，你就可以去解题。解题思路如下：
1、我们先把每个部门的每个员工对应的薪资查询出来，得到员工与薪资的一个结果集。
2、使用窗口函数，给每个部门的薪资进行排序，
3、在2的结果集前提下，查询row_number为1的结果就是我们想要的每个部门的最高薪资的员工信息。
需要注意的是，窗口函数只有在mysql8.0上才有，如果在8.0版本前的话，需要用到别的方法。

这是测试用例：

drop table if exists  `dept_emp` ; 
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 `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 salaries VALUES(10001,88958,'2002-06-22','9999-01-01');
INSERT INTO salaries VALUES(10002,72527,'2001-08-02','9999-01-01');

预期输入:d001|10001|88958

如果你的结果与预期输入的一致，说明你的运行结果成功了。

使用窗口函数查询sql语句如下

SELECT
	dept_no,
	emp_no,
	salary 
FROM
	(
	SELECT
		emp_no,
		dept_no,
		salary,
		ROW_NUMBER ( ) OVER ( PARTITION BY dept_no ORDER BY salary DESC ) AS "Row_Number" 
	FROM
		(
		SELECT
			d.emp_no,
			d.dept_no,
			s.salary 
		FROM
			dept_emp d
			INNER JOIN salaries AS s ON d.emp_no = s.emp_no 
		WHERE
			d.to_date = '9999-01-01' 
			AND s.to_date = '9999-01-01' 
		) AS a 
	) AS b 
WHERE
	b.Row_Number = 1

mysql8.0以下版本的sql语句

SELECT D.dept_no,D.emp_no,D.salary FROM(
    SELECT (
        SELECT COUNT( DISTINCT F.salary ) 
            FROM(
                  SELECT
                    A.dept_no,
                    A.emp_no,
                    B.salary 
                  FROM
                    dept_emp A
                    INNER JOIN salaries B ON A.emp_no = B.emp_no 
                  WHERE
                    A.to_date = '9999-01-01' 
                    AND B.to_date = '9999-01-01' 
                ) AS F 
        WHERE
            F.salary >= C.salary 
            AND F.dept_no = C.dept_no 
        ) AS raking,
           C.dept_no,C.emp_no,C.salary FROM (
            SELECT
                A.dept_no,
                A.emp_no,
                B.salary
            FROM 
                dept_emp A
            INNER JOIN salaries B ON A.emp_no = B.emp_no
            WHERE 
            A.to_date = '9999-01-01'
            AND B.to_date = '9999-01-01' ) C ) D 
WHERE D.raking = 1
ORDER BY D.dept_no

对比明显，使用窗口函数比较简单明了。。。希望这里可以帮助到大家。。。