文章目录
  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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
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 <= NRowNumber = N 即可,所以也适合题目 #185 Department Top Three Salaries

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
-- 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

构造测试数据

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
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');

本博客微信公众号

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