Leetcode Database: #185 Department Top Three Salaries

文章目录

1. 1. 题目
2. 2. 注意事项
3. 3. 解题思路&一种答案
   1. 3.1. 第一步
   2. 3.2. 第二步
4. 4. 构造测试数据

题目

#185 Department Top Three Salaries

注意事项

• Salary 排序要求是，例如前两名 Salary 相同，那么接着第三人 Salary 排名是 2

  所以计算排名的方法有三种情况

• 第一步中不能直接 WHERE RowNumber <= 3，因为 SQL 计算顺序是 FROM > WHERE > GROUP BY > HAVING > SELECT > ORDER BY

  临时列 RowNumber 在计算 WHERE 时这个列尚未生成，所以需要再在外层 SELECT 一次

解题思路&一种答案

第一步

这题和前面一题 Department Highest Salary 比较类似，我们慢慢分步解决，首先是找出各组前 3 大值。这里我们要老实的用自定义变量做 ROW_NUMBER。

排名要考虑三种情况，当 grpId 变化时，RowNumber 重置为 1；当 Salary 值小于前值，RowNumber + 1；当 Salary 等于前值，RowNumber 不变。所以需要使用 CASE WHEN。

SELECT 
    Salary,
    Name,
    DepartmentId,
    CASE
        WHEN
            @grpId = DepartmentId
                AND Salary < @prevVal
        THEN
            @rowNumber:=@rowNumber + 1
        WHEN
            @grpId = DepartmentId
                AND Salary >= @prevVal
        THEN
            @rowNumber:=@rowNumber
        ELSE @rowNumber:=1
    END RowNumber,
    @grpId:=DepartmentId,
    @prevVal:=Salary
FROM
    Employee,
    (SELECT @rowNumber:=1) x,
    (SELECT @grpId:=NULL) y,
    (SELECT @prevVal:=NULL) z
ORDER BY DepartmentId , Salary DESC

其结果为：

Salary	Name	DepartmentId	RowNumber	@grpId:=DepartmentId	@prevVal:=Salary
90000	Max	1	1	1	90000
85000	Randy	1	2	1	85000
70000	Joe	1	3	1	70000
70000	Tom	1	3	1	70000
69000	Janet	1	4	1	69000
80000	Henry	2	1	2	80000
60000	Sam	2	2	2	60000

第二步

我们还需要对应的 Department.Name，那就再和 Department 表 INNER JOIN 一次；我们还需要对筛选第一步的结果，只要 RowNumber <= 3。这两个操作可以在一个 SELECT 当中完成，就是最终结果。这个答案可以推广到 TOP N 或者 Nth，分别把条件改为 RowNumber <= N 或 RowNumber = N 即可，所以也适合题目 #185 Department Top Three Salaries

-- Runtime: 1206 ms
SELECT 
    dep.Name AS Department, tmp.Name AS Employee, Salary
FROM
    Department dep
        INNER JOIN
    (SELECT 
        Id,
            Salary,
            Name,
            DepartmentId,
            CASE
                WHEN
                    @grpId = DepartmentId
                        AND Salary < @prevVal
                THEN
                    @rowNumber:=@rowNumber + 1
                WHEN
                    @grpId = DepartmentId
                        AND Salary >= @prevVal
                THEN
                    @rowNumber:=@rowNumber
                ELSE @rowNumber:=1
            END RowNumber,
            @grpId:=DepartmentId,
            @prevVal:=Salary
    FROM
        Employee, (SELECT @rowNumber:=1) x, (SELECT @grpId:=NULL) y, (SELECT @prevVal:=NULL) z
    ORDER BY DepartmentId , Salary DESC) tmp ON tmp.DepartmentId = dep.Id
WHERE
    RowNumber <= 3

得到预期答案：

Department	Name	Salary
IT	Max	90000
IT	Randy	85000
IT	Joe	70000
IT	Tom	70000
Sales	Henry	80000
Sales	Sam	60000

构造测试数据

CREATE TABLE IF NOT EXISTS Employee (
    Id INT,
    Name VARCHAR(20),
    Salary INT,
    DepartmentId INT
);

CREATE TABLE IF NOT EXISTS Department (
    Id INT,
    Name VARCHAR(20)
);

DELETE FROM Employee;
DELETE FROM Department;

INSERT INTO Employee VALUES
(1, 'Joe'  , 70000, 1),
(2, 'Henry', 80000, 2),
(3, 'Sam'  , 60000, 2),
(4, 'Max'  , 90000, 1),
(5, 'Janet'  , 69000, 1),
(6, 'Randy'  , 85000, 1),
(7, 'Tom'  , 70000, 1);

INSERT INTO Department VALUES
(1, 'IT '),
(2, 'Sales');

---

本博客微信公众号