mysql使用sql实现排行榜
1.mysql 8.0 之后:
SELECT id, name, amount
, ROW_NUMBER() OVER (ORDER BY amount desc)
, RANK() OVER (ORDER BY amount desc)
, DENSE_RANK() OVER (ORDER BY amount desc)
FROM t_amount;
ROW_NUMBER()返回每行开始的唯一编号;
Rank()返回从1开始的每行的唯一编号,除了有关系时,等级()将分配相同的数字。同样,差距将遵循重复的等级。Rank()存在重复时,排行如下:1 2 2 4 5 6 7
dense_rank()类似于等级(),除了重复等级后没有间隙。 dense_rank()存在重复时,排行如下:1 2 2 3 4 5 6
2.mysql 8.0之前
SELECT id, name, amount,
@row_number := @row_number + 1 AS row_number,
(SELECT COUNT(DISTINCT amount) FROM t_amount t2 WHERE t2.amount >= t1.amount) AS rank,
(SELECT COUNT(DISTINCT amount) FROM t_amount t2 WHERE t2.amount > t1.amount) + 1 AS dense_rank
FROM t_amount t1
CROSS JOIN (SELECT @row_number := 0) AS rn
ORDER BY amount DESC;
执行结果如下
mysql 8.0之前使用如上sql,rank和dense_rank的效果一样,重复时,会重复排名,但是不会跳过间隔。