最近在做数据库的练习,一道LeetCode 上的题目花了我一个小时分析不同解法,在此记录一下。
题目描述
Write a SQL query to get the nth highest salary from the Employee table.
+----+--------+
| Id | Salary |
+----+--------+
| 1 | 100 |
| 2 | 200 |
| 3 | 300 |
+----+--------+
For example, given the above Employee table, the nth highest salary where n = 2 is 200. If there is no nth highest salary, then the query should return null.
+------------------------+
| getNthHighestSalary(2) |
+------------------------+
| 200 |
+------------------------+
题目告诉你有一个表格,让你输出第N大的工资
题目分析
一般来说,简单的题目都是有坑的。。这道提也不例外,几个坑如下:
1. 如果不存在输出Null
2. 不存在的情况有:
表格数据没有N项
* 表格中有N项,但是非重复的没有N项*
很多人忘记这个情况,导致解题错误
解题思路
- 直接调包思路
直接利用limit 函数和偏移项
CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
DECLARE M INT;
SET M=N-1;
RETURN (
# Write your MySQL query statement below.
SELECT DISTINCT Salary FROM Employee
ORDER BY Salary DESC LIMIT M, 1
);
END
需要注意的是:
limit偏移项两种写法相反
语句1:select * from table limit 9,4
语句2:slect * from table limit 4 offset 9
// 语句1和2均返回表student的第10、11、12、13行
//语句2中的4表示返回4行,9表示从表的第10行开始
N-1直接写会报错
这也是直接定义
DECLARE M INT;
SET M=N-1;
的原因。你也可以写成:
SET N=N-1;
一开始我忽略了表格中有N项,但是非重复的没有N项的情况
这也是代码中DISTINCT函数存在的意义。
2.手动实现
我们可以直接从原理出发自己实现,因为我们要求第N大的工资,我们可以先对整个表排序,然后取前N-1项,再与原表求差集,这样得到的表格最大值就是我们要求的原表第N大,但是由于MYSQL没有提供求差集和求交集的函数,我们只能用求并集的方式自己实现一个,直接上代码:
CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
set N=N-1;
RETURN (
# Write your MySQL query statement below.
select max(Salary) from
(
select Salary from
(
select distinct Salary from Employee A
UNION ALL
(select distinct Salary from Employee B
order by Salary DESC limit N)
)D GROUP BY Salary HAVING COUNT(Salary) = 1
)E
);
END
需要注意的仍然是distinct关键字,然后mysql要求每个派生表都要有别名,即使用不到,你也要给一个名字。
除此之外,你还可以手动实现排序:
CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
RETURN (
# Write your MySQL query statement below.
SELECT A.Salary
FROM (SELECT DISTINCT Salary FROM Employee) A
WHERE (SELECT COUNT(*) FROM (SELECT DISTINCT
Salary FROM Employee) B WHERE
A.Salary > B.Salary) = N - 1
LIMIT 1
);
END
* 所有代码可以在我的github找到:*
https://github.com/yang1young/LeetCode