MYSQL实现排名及查询指定用户排名功能

前言：mysql没有实现类似排名(rank)功能的函数。但是我们可以通过基数的查询加上其他函数可是实现类似的功能。
          MYSQL实现排名及查询指定用户排名功能（并列排名功能）

CREATE TABLE test.testsort (  id int(11) NOT NULL AUTO_INCREMENT,  uid int(11) DEFAULT 0 COMMENT '用户id',  score decimal(10, 2) DEFAULT 0.00 COMMENT '分数',  PRIMARY KEY (id)   )  ENGINE = INNODB AUTO_INCREMENT = 1     CHARACTER SET utf8  COLLATE utf8_general_ci  COMMENT = '测试排序'   ROW_FORMAT = DYNAMIC;    


INSERT INTO `testsort` (`id`, `uid`, `score`) VALUES(1, 10001, 25),
(2, 10002, 20),
(3, 10003, 20),
(4, 10008, 22),
(5, 10020, 21),
(6,10099, 24),
(7, 10100, 25),
(8, 10102, 26),
(9, 10104, 23),
(10, 10109, 19),
(11,10200, 20),
(12, 10223, 20);

1.实现排名：
SELECT t.*, @rownum := @rownum + 1 AS rownum FROM (SELECT @rownum := 0) r, testsort AS t  ORDER BY t.score DESC;   
查看指定用户排名：
SELECT b.* from (  SELECT t.*, @rownum := @rownum + 1 AS rownum FROM (SELECT @rownum := 0) r, testsort AS t ORDER BY t.score DESC ) as b where b.uid =10008;   


2.实现并列排名（相同分数排名相同）：
SELECT obj.uid, obj.score,  CASE  WHEN @rowtotal = obj.score THEN @rownum  WHEN @rowtotal := obj.score THEN @rownum :=@rownum + 1 WHEN @rowtotal = 0 THEN  @rownum :=@rownum + 1     END AS rownum FROM (  SELECT uid, score  FROM testsort ORDER BY score DESC ) AS obj,  (SELECT @rownum := 0 ,@rowtotal := NULL) r    

查询指定用户并列排名:
 SELECT total.* FROM ( SELECT obj.uid, obj.score,  CASE WHEN @rowtotal = obj.score THEN  @rownum WHEN @rowtotal := obj.score THEN @rownum :=@rownum + 1 WHEN @rowtotal = 0 THEN  @rownum :=@rownum + 1   END AS rownum     FROM  ( SELECT uid,score FROM  testsort     ORDER BY     score DESC ) AS obj,  ( SELECT @rownum := 0 ,@rowtotal := NULL ) r ) AS total WHERE total.uid = 10008;    

参考网站：https://wenda.so.com/q/1541351540210058
                https://blog.csdn.net/weixin_30313365/article/details/113189183