第N高的薪水和排名问题

第二高的薪水.png

求第二高的薪水和第N高的薪水没有区别，主要注意的是若有相同的薪水的时候后面是否跳过，这里使用了子查询。这里的是相同名次的进行了跳过，如果相同名次出现后，后面的不跳过，可以把COUNT(P2.id),改成COUNT(DISTINCT P2.salary).同样可以把子查询改成非等值自连接的方式。没有想出来的地方是在查询不存在的时候返回null，个人印象中null，null除了在数据表默认的地方出现，另一个也就是join不到的情况了，所以后来生搬硬套凑出一个left join条件。

方案1:子查询

SELECT c.Salary SecondHighestSalary  FROM (SELECT 2 as RANK) D  LEFT JOIN 
(SELECT P1.Id,P1.salary,(SELECT COUNT(P2.Id) FROM  Employee P2 WHERE P2.salary > P1.salary) + 1 AS rank 
FROM Employee P1) C ON  C.rank = D.rank GROUP BY D.rank

方案2：非等值自连接

SELECT c.Salary SecondHighestSalary  FROM (SELECT 2 as RANK) D  LEFT JOIN 
(SELECT P1.Id,P1.salary,COUNT(P2.salary) + 1 AS rank 
FROM Employee P1 JOIN Employee P2 ON P2.salary > P1.salary GROUP BY P1.id) C ON  C.rank = D.rank GROUP BY D.rank

第N高的薪水

CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
  RETURN (
SELECT c.Salary SecondHighestSalary  FROM (SELECT N as RANK) D  LEFT JOIN 
(SELECT P1.Id,P1.salary,(SELECT COUNT(DISTINCT P2.Salary) FROM  Employee P2 WHERE P2.salary > P1.salary) + 1 AS rank 
FROM Employee P1) C ON  C.rank = D.rank GROUP BY D.rank
  );
END

还有一个排名问题，和这里几乎一样。

image.png

SELECT s1.Score,COUNT(DISTINCT s2.Score)+1 rank FROM
Scores s1 LEFT JOIN Scores s2 ON s1.Id != s2.Id AND s1.Score < s2.Score
 GROUP BY s1.Id order by rank