【MySQL】排序取Top

leetcode 176：

力扣 176

第二高理解为相同薪水同一等级，且连续排序

SELECT
    IFNULL(
      (SELECT DISTINCT Salary
       FROM Employee
       ORDER BY Salary DESC
        LIMIT 1 OFFSET 1),
    NULL) AS SecondHighestSalary

拓展->分组排序

• ROW_NUMBER：它为从1开始应用的每一行分配一个序号，递增

ROW_NUMBER() OVER (
<PARTITION BY <expression>,[{,<expression>}...] >
 <ORDER BY <expression> [ASC|DESC],[{,<expression>}...]>) 

例：
SELECT 
 ROW_NUMBER() OVER (
 ORDER BY productName
 ) row_num,
    productName,
    msrp
FROM 
 products
ORDER BY 
 productName; 

• RANK()函数为结果集的分区中的每一行分配一个排名，行的等级由1加上前面的等级数指定。同等至排序相同，下一等级跳跃排序，如排序结果图

RANK() OVER (
    PARTITION BY <expression>[{,<expression>...}]
    ORDER BY <expression> [ASC|DESC], [{,<expression>...}]
) 

例：
SELECT
    val,
    RANK() OVER (
        ORDER BY val
    ) my_rank
FROM
    rankDemo; 

排序结果

• DENSE_RANK()：为分区或结果集中的每一行分配排名，而排名值没有间隙。

DENSE_RANK() OVER (
    PARTITION BY <expression>[{,<expression>...}]
    ORDER BY <expression> [ASC|DESC], [{,<expression>...}]
) 

例：
SELECT
    val,
    DENSE_RANK() OVER (
        ORDER BY val
    ) my_rank
FROM
    rankDemo; 

排序结果

对比

val	ROW_NUMBER	RANK()	DENSE_RANK()
1	1	1	1
2	2	2	2
2	3	2	2
3	4	4	3
4	5	5	4
4	6	5	4
5	7	7	5