SQL实现LeetCode(177.第N高薪水)
16lz
2021-12-10
[LeetCode] 177.Nth Highest Salary 第N高薪水
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.
这道题是之前那道Second Highest Salary的拓展,根据之前那道题的做法,我们可以很容易的将其推展为N,根据对Second Highest Salary中解法一的分析,我们只需要将OFFSET后面的1改为N-1就行了,但是这样MySQL会报错,估计不支持运算,那么我们可以在前面加一个SET N = N - 1,将N先变成N-1再做也是一样的:
解法一:
CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INTBEGIN SET N = N - 1; RETURN ( SELECT DISTINCT Salary FROM Employee GROUP BY Salary ORDER BY Salary DESC LIMIT 1 OFFSET N );END
解法二:
CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INTBEGIN RETURN ( SELECT MAX(Salary) FROM Employee E1 WHERE N - 1 = (SELECT COUNT(DISTINCT(E2.Salary)) FROM Employee E2 WHERE E2.Salary > E1.Salary) );END
解法三:
CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INTBEGIN RETURN ( SELECT MAX(Salary) FROM Employee E1 WHERE N = (SELECT COUNT(DISTINCT(E2.Salary)) FROM Employee E2 WHERE E2.Salary >= E1.Salary) );END
Second Highest Salary
参考资料:
https://leetcode.com/discuss/88875/simple-answer-with-limit-and-offset
https://leetcode.com/discuss/63183/fastest-solution-without-using-order-declaring-variables
更多相关文章
- MySQL系列多表连接查询92及99语法示例详解教程
- Linux下MYSQL 5.7 找回root密码的问题(亲测可用)
- MySQL 什么时候使用INNER JOIN 或 LEFT JOIN
- Android(安卓)-- Android(安卓)JUint 与 Sqlite
- android中SqLite query中用selectionArgs处理字符传值
- android从服务器下载文件(php+apache+win7+MySql)
- Android(安卓)ORM SQL Top 5
- android SQLiteDatebase 实践
- Android(安卓)SQLiteDatabase的使用