mysql查询排行后三分之一 mysql根据分数排名次

现在在一般的项目中实现排名的功能应该非常常见，例如在一个班级中，学生的数学分数进行排名，取排名靠前10个学生的信息，再例如获取我在班级中的排名等等

1. 创建表

CREATE TABLE `com_user_point` ( `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键id', `gmt_create` datetime NOT NULL COMMENT '创建时间', `gmt_modified` datetime NOT NULL COMMENT '修改时间', `user_id` bigint(20) NOT NULL COMMENT '用户id', `point` int(2) NOT NULL COMMENT '分数', PRIMARY KEY (`id`), UNIQUE KEY `uk_userid` (`user_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8；

这个表结构非常简单，只有用户user_id 和 分数point两个字段；

2. 实现排名功能方式

    2.1 方式一

          SELECT b.id AS id,b.user_id AS userId,b.point AS POINT,b.rownum AS rank FROM
          (
               SELECT t.*, @rownum := @rownum + 1 AS rownum
               FROM (SELECT @rownum := 0) r, com_user_point AS t
               ORDER BY t.point DESC
         ) AS b LIMIT 0,20; 

上面sql相关解释：查询com_user_point表中 根据point降序排序，然后分页

          @rownum := @rownum + 1 的作用类似于：a=a+1,都是赋值的作用

          @rownum := 0) r 的作用是设置@rownum的默认值为0，即编号从1开始

rownum 列就是名次了，sql的逻辑就是将rownum 变量初始化为0，然后每查出一条数据，将rownum + 1并赋值给rownum

sql执行结果如下：

    2.2 方式二

          SELECT t.id AS id,t.user_id AS userId,t.point AS POINT, @rownum := @rownum + 1 AS rank
          FROM (SELECT @rownum := 0) r, (SELECT * FROM com_user_point ORDER BY POINT DESC) AS t;

sql执行结果如下：

       

如果 要跟上查询条件的话，sql语句可以改造为：

方式一：
SELECT b.id AS id,b.user_id AS userId,b.point AS POINT,b.rownum AS rank FROM
 (
 SELECT t.*, @rownum := @rownum + 1 AS rownum
 FROM (SELECT @rownum := 0) r, com_user_point AS t
 ORDER BY t.point DESC
 ) AS b WHERE b.user_id = 1245; 
 
 方式二：
SELECT t.id AS id,t.user_id AS userId,t.point AS POINT, @rownum := @rownum + 1 AS rank
 FROM (SELECT @rownum := 0) r, (SELECT * FROM com_user_point ORDER BY POINT DESC) AS t
 WHERE t.user_id = 1245;

但是方式二有个问题：rank不对，看方式二的sql执行结果：rank应该是2，所以方式二不可行

2.3 方式三

     上面的两种方式有一个很明显的缺陷，就是两个人的分数一样，但是，两个人的排名不一样，如果想要分数一样，排名一样，就采用下面的方式：

 

SELECT 
   temp.id AS id,
   temp.user_id AS userId,
   temp.point AS POINT,
   temp.rank AS rank 
 FROM
   (SELECT 
     comp.id,comp.user_id,comp.point,
     (
       CASE
         WHEN @temp_point = comp.point 
         THEN @rank 
         WHEN @temp_point := comp.point 
         THEN @rank := @rank + 1 
         WHEN @temp_point = 0 
         OR @temp_point IS NULL 
         THEN @rank := @rank + 1 
       END
     ) AS rank 
   FROM
     (SELECT 
       id,
       user_id,
       POINT 
     FROM
       com_user_point 
     ORDER BY POINT DESC) AS comp,
     (SELECT 
       @rank := 0,
       @temp_point := NULL) r) AS temp LIMIT 0,20;

上面sql名词解释：rank还是排名，temp_point的作用是保存名次变化时的分数值，也可以理解为上一次的分数值，当上一个的分数和当前分数一样的时候，rank不变，不一样的时候，rank+1，另外之所以加一下temp_point= 0或是NULL的情况，是因为mysql用户变量不能赋0或NULL。

执行结果如下：可以看到分数一样的两个人，排名也一样

      

如果想要添加过滤查询条件，可以改造如下方式：

SELECT 
   temp.id AS id,
   temp.user_id AS userId,
   temp.point AS POINT,
   temp.rank AS rank 
 FROM
   (SELECT 
     comp.id,comp.user_id,comp.point,
     (
       CASE
         WHEN @temp_point = comp.point 
         THEN @rank 
         WHEN @temp_point := comp.point 
         THEN @rank := @rank + 1 
         WHEN @temp_point = 0 
         OR @temp_point IS NULL 
         THEN @rank := @rank + 1 
       END
     ) AS rank 
   FROM
     (SELECT 
       id,
       user_id,
       POINT 
     FROM
       com_user_point 
     ORDER BY POINT DESC) AS comp,
     (SELECT 
       @rank := 0,
       @temp_point := NULL) r) AS temp WHERE temp.user_id = 1245;

sql执行结果如下：

2.4 方式四

  如果两个人的分数一样，排名一样，并且占用名次，也就说，两个人都是60分，都是第10名，那么下一个人(假如59分)，他的排名是12.

SELECT
     temp.id AS id,
     temp.user_id AS userId,
     temp.point AS POINT,
     temp.rank AS rank 
     FROM
     (
         SELECT
             ta.id,ta.user_id,ta.point,
             @index := @index + 1,
         @rank := (CASE
         WHEN @temp_point = ta.point THEN
             @rank
         WHEN @temp_point := ta.point THEN
             @index
         WHEN @temp_point = 0 OR @temp_point IS NULL THEN
             @index
         END) AS rank
         FROM
         (
         SELECT 
               id,
               user_id,
               POINT 
           FROM
          com_user_point 
           ORDER BY POINT DESC
         ) AS ta,
         ( SELECT @rank := 0 ,@rowtotal := NULL ,@index := 0 ) r
     ) AS temp;

这一次就是增加一个index变量，只要有数据就加1，当temp_point 不等于point 时，就赋值index给rank，并将rank变量保存，当一样时，就继续使用rank

sql执行结果如下：

注意：以上方案在DRDS中不适用，DRDS不支持这种语法的使用。