MySql 数据排序

创建示例表

CREATE TABLE `test` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(32) DEFAULT NULL,
  `score` double(12,2) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=18 DEFAULT CHARSET=utf8mb4;

填充数据

image-20200827183328251

简单排序

SELECT v.* ,
    CASE 
     WHEN @s = 0 THEN @rk := @rk + 1
    END AS rank
FROM 
(
    SELECT * FROM test t ORDER BY score DESC
) v,
(SELECT @s:=0,@rk:=0) w

image-20200827183356765

相同分数并列

SELECT v.id,v.name ,v.score AS score ,
    CASE 
     WHEN @s = 0 THEN @rk :=  1  
     WHEN @s = v.score THEN @rk := @rk
     WHEN @s > v.score THEN @rk := @rk + 1
    END AS rank ,@s := score AS bak
FROM 
(
    SELECT * FROM test t ORDER BY score DESC
) v, 
(SELECT @s :=0,@rk :=0) w;  

image-20200827185159831