LeetCode---SQL刷题6
目录:
- 1.177. 第N高的薪水
- 2.178. 分数排名
- 3.180. 连续出现的数字
- 4.181. 超过经理收入的员工
1.177. 第N高的薪水
SQL语句如下:
CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
SET N=N-1;
RETURN (
# Write your MySQL query statement below.
SELECT DISTINCT salary FROM Employee
ORDER BY salary DESC
LIMIT N,1
);
END
备注:对数据进行排序并去重,然后通过LIMIT来获取用户需要的数据。
2.178. 分数排名
SQL语句如下:
- 解题1
SELECT s1.score,
(SELECT COUNT(DISTINCT s2.score) FROM Scores s2 WHERE s2.score >= s1.score)
AS 'rank'
FROM Scores s1
ORDER BY s1.score DESC
- 解题2
SELECT score,DENSE_RANK() OVER (
ORDER BY score DESC
) AS 'rank'
FROM Scores
备注:解题1中未使用排名函数,其中取别名的时候不能直接使用rank因为存在rank函数,所以用字符表示,解题2中使用排名函数要求MySQL环境在MySQL8.0及以上,关于DENSE_RANK()的说明,请参照这篇博文https://www.begtut.com/mysql/mysql-dense_rank-function.html,其中未使用RANK()和ROW_NUMBER()函数,因为只有DENSE_RANK()函数才是并列连续排序,关于RANK，DENSE_RANK和ROW_NUMBER三者的区别你可以通过这篇博文https://blog.csdn.net/u011726005/article/details/94592866进行学习。
3.180. 连续出现的数字
SQL语句如下:
- 解题1
SELECT DISTINCT num AS ConsecutiveNums FROM Logs
WHERE (id+1,num) IN (SELECT * FROM Logs)
AND (id+2,num) IN (SELECT * FROM Logs)
- 解题2
SELECT DISTINCT l1.num AS ConsecutiveNums FROM Logs l1,Logs l2,Logs l3
WHERE l1.id =l2.id - 1 AND l2.id = l3.id -1 AND l1.num = l2.num AND l2.num = l3.num
4.181. 超过经理收入的员工
SQL语句如下:
- 解法1
SELECT
e1.name
AS Employee
FROM Employee e1,Employee e2
WHERE e1.managerId =e2.id AND e1.salary > e2.salary
- 解法2
SELECT
e1.name
AS Employee
FROM Employee e1
LEFT JOIN Employee e2 ON e1.managerId =e2.id
WHERE e1.salary > e2.salary