SQL 实现位次排序

• 使用窗口函数实现位次排序（MySQL不适用）

SELECT name, price,
    RANK() OVER ( ORDER BY price DESC ) AS rank_1,
    DENSE_RANK() OVER ( ORDER BY price DESC) AS rank_2
  FROM Products;

执行结果

name	price	rank_1	rank_2
橘子	100	1	1
西瓜	80	2	2
苹果	50	3	3
香蕉	50	3	3
葡萄	50	3	3
柠檬	30	6	4

• 非等值自连接实现方式rank_1

SELECT P1.name,
    P1.price,
    ( SELECT COUNT(P2.price)
        FROM Products P2
        WHERE P2.price > P1.price) + 1 AS rank_1
FROM Products P1
ORDER BY rank_1;

说明：如果price中存在null值，那么含null值的列序号为1。

• 非等值自连接实现方式rank_2

SELECT P1.name,
    P1.price,
    ( SELECT COUNT(DISTINCT P2.price)
        FROM Products P2
        WHERE P2.price > P1.price) + 1 AS rank_1
FROM Products P1
ORDER BY rank_1;

说明：在查询rank_1方式的子查询中加入DISTINCT 即可。