LeetCode---SQL刷题6

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