分数Rank排名SQL

描述

分数表中，如两个分数相同，则两个分数的rank相同，将分数按rank值顺序排列；

建表、插入数据

-- 1、建表
CREATE TABLE `test_score` (
  `id` varchar(40) NOT NULL COMMENT '主键',
  `score` float(10,2) DEFAULT NULL COMMENT '分数',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
-- 2、插入数据
INSERT INTO `test_score` (`id`, `score`) VALUES ('111', 82.50);
INSERT INTO `test_score` (`id`, `score`) VALUES ('222', 34.50);
INSERT INTO `test_score` (`id`, `score`) VALUES ('333', 60.00);
INSERT INTO `test_score` (`id`, `score`) VALUES ('444', 60.00);
INSERT INTO `test_score` (`id`, `score`) VALUES ('555', 59.00);
INSERT INTO `test_score` (`id`, `score`) VALUES ('666', 90.00);
INSERT INTO `test_score` (`id`, `score`) VALUES ('777', 90.00);

功能SQL

• 方式一

SELECT
    a.id,
    a.score AS score,
    ( SELECT count( DISTINCT b.score ) FROM test_score b WHERE b.score >= a.score) AS Rank 
FROM
    test_score a 
ORDER BY
    a.score DESC;

• 方式二

SELECT
    t.id,
    t.score,
    tb.rank 
FROM
    `test_score` t,
    (
    SELECT
        ta.score,
        ( @rowNum := @rowNum + 1 ) AS rank 
    FROM
        ( SELECT DISTINCT score FROM `test_score` ORDER BY score DESC ) ta,
        ( SELECT @rowNum := 0 ) setrownum 
    ) tb 
WHERE
    t.score = tb.score 
ORDER BY
    tb.rank ASC,
    t.id ASC

处理结果

说明

• :=表示赋值，可作用于set、update、select；
• @标识符表示用户变量字段，为了区分系统变量、字段、用户自定义变量，需要在用户变量前，增加@标识符，否则在上述sql中会被理解为一个字段。