title: MySQL 中使用变量实现排名名次
date: 2023-7-16 19:45:26
tags:
- SQL 高级查询
一. 数据准备:
CREATE TABLE sql_rank (
id INT ( 11 ) UNSIGNED NOT NULL AUTO_INCREMENT,
user_id INT ( 11 ) UNSIGNED NOT NULL,
score TINYINT ( 3 ) UNSIGNED NOT NULL,
add_time date NOT NULL,
PRIMARY KEY ( id )
) ENGINE = INNODB CHARSET = latin1;
INSERT INTO sql_rank ( user_id, score, add_time )
VALUES
( 100, 50, '2016-05-01' ),
( 101, 30, '2016-05-01' ),
( 102, 20, '2016-05-01' ),
( 103, 60, '2016-05-01' ),
( 104, 80, '2016-05-01' ),
( 105, 50, '2016-05-01' ),
( 106, 70, '2016-05-01' ),
( 107, 85, '2016-05-01' ),
(
108,
60,
'2016-05-01');
二. 不管数据相同与否,排名依次排序(1,2,3,4,5,6,7,...)
思路: 将已经排序好的数据从第一条依次取出来,取一条就自增加一,实现从 1 到最后的一个排名
SELECT
obj.user_id,
obj.score,
@rownum := @rownum + 1 AS rownum
FROM
( SELECT user_id, score FROM sql_rank ORDER BY score DESC ) obj,
( SELECT @rownum := 0 ) r
三. 只要数据有相同的排名就一样,排名依次排序(1,2,2,3,3,4,5,...)
思路: 当出现相同的数据时,排名保持不变,此时则需要再设置一个变量,用来记录上一条数据的值,跟当前数据的值进行对比,如果相同,则排名不变,不相同则排名自增加 1
SELECT
obj.user_id,
obj.score,
CASE
WHEN @prerow = obj.score THEN
@currank
WHEN @prerow := obj.score THEN
@currank := @currank + 1
WHEN @prerow = 0 THEN
@currow := @currank + 1
END AS currank
FROM
( SELECT user_id, score FROM sql_rank ORDER BY score DESC ) obj,
( SELECT @currank := 0, @prerow := NULL ) r
让我们逐行解释:
WHEN @prerow = obj.score THEN @currank
: 这行代码检查前一行的分数是否与当前行的分数相同。如果相同,则将当前的排名(@currank)赋值给当前行的排名。WHEN @prerow := obj.score THEN @currank := @currank + 1
: 这行代码首先将当前行的分数赋值给@prerow变量,然后将当前排名(@currank)加1。WHEN @prerow = 0 THEN @currow := @currank + 1
: 这行代码检查前一行的分数是否为0。如果是,则将当前排名(@currank)加1,并将结果赋值给@currow变量。END AS currank
: 这行代码将计算得到的排名赋值给一个名为currank的列。