分数Rank排名SQL

·  阅读 261
分数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);
复制代码

image.png

功能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
复制代码

处理结果

image.png

说明

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