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