mysql 加序号以及成绩单排名的几种实现方式

首先   我们新建一个表，表信息如下：

CREATE TABLE `report` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT 'id',
  `score` int(4) DEFAULT NULL COMMENT '分数',
  `user_name` varchar(255) DEFAULT NULL COMMENT '姓名',
  `exam_time` time DEFAULT NULL COMMENT '考试时间',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8 COMMENT='成绩表';

假如是一般的排序 ，那我们直接按照分数降序排序，SELECT * FROM report ORDER BY score DESC；

查出来一个list，list的索引就当做它的排名（当然是索引+1）。

或者直接用sql，在sql里标明排名序号：

SELECT
  @rownum := @rownum + 1 AS rownum,
  r.*
FROM
  report r,
  (SELECT @rownum := 0) b
ORDER BY
  score DESC

上面的@方式是mysql定义变量的方式，用：=进行赋值。

这两种方法都是简单的进行降序排序，然后加上序号，显然很low，也不符合需求。

一般的成绩单呢，应该是分数相同的，排名一致，以此类推。所以，我们给出三种查询方式：

方式一：借助建立伪表，比较大小并排序

SELECT
    count(b.user_name) + 1 AS rk,
    a.*
FROM
    report a
LEFT JOIN report b ON a.score < b.score
GROUP BY
    a.user_name
ORDER BY
    count(b.user_name) + 1;

方式二： 借助于if函数判断以及自定义方式

SELECT 
@rownum:=@rownum+1 AS rownum,
if(@score=A.score,@rank,@rank:=@rownum)as rank,
@score:=A.score,
A.user_name,A.score,A.exam_time
FROM
(SELECT * FROM report ORDER BY score DESC) A,
(SELECT @rank:=0,@rownum:=0,@score:=null)B 

方式三：借助case when函数判断：

select 
count(case when a.score < b.score then b.user_name else null end)+1 as rank,
a.user_name,a.score,a.exam_time
from report a 
left join report b 
on 1=1
group by a.user_name,a.score,a.exam_time
order by rank

  以上三种方式的结果大概意思是这样：如下图

  到此   成绩单的排序，就基本满足普通的需求了。

 

 

由于博主本人又遇到了新需求，对于一些小测验啥的，要求，分数一样的，按时间长短排序，分数时间都一样，排名形同。

这样的话，其实就是对以上三种方法再加些判断条件。

对于第一种的话，就稍微复杂一点。如下：

方式A：

select a.rk+count(b.user_name) as rk,a.user_name,a.score,a.exam_time
from 
(
  select a.user_name,a.exam_time,a.score,count(b.user_name)+1 as rk
  from report a 
  left join report b on a.score < b.score 
  group by a.user_name
  order by count(b.user_name)+1
)a 
left join
(
  select a.user_name,a.exam_time,a.score,count(b.user_name)+1 as rk
  from report a 
  left join report b on a.score < b.score 
  group by a.user_name
  order by count(b.user_name)+1
)b 
on a.rk = b.rk 
and a.exam_time > b.exam_time
group by a.user_name
order by a.rk+count(b.user_name)

相当于是在第一种方式的基础上，套了一大层当做表，然后再做判断，当然对于第一种应该还有更优化的方法，欢迎大家来写出自己的想法。结果如下图：

这样的写法，乍一看，代码有点多，那么对于用函数的，怎么查询呢？

方式B：

SELECT 
@rownum:=@rownum+1 AS rownum,
if((@score=A.score && @exam_time=A.exam_time),@rank,@rank:=@rownum)as rank,
@score:=A.score,@exam_time:=A.exam_time,
A.user_name
FROM
(
SELECT * FROM report ORDER BY score DESC,exam_time ASC
) A,
(SELECT @rank:=0,@rownum:=0,@score:=NULL,@exam_time:=NULL)B

这个方法的结果如下图：

 

那么 ，假如想要查询某个人的信息以及排名呢，所有人的都查出来了，查某个人的，就加个判断条件啦

SELECT C.*
FROM
(
SELECT 
@rownum:=@rownum+1 AS rownum,
if(@score=A.score,@rank,@rank:=@rownum)as rank,
@score:=A.score,
A.user_name,A.score,A.exam_time
FROM
(SELECT * FROM report ORDER BY score DESC) A,
(SELECT @rank:=0,@rownum:=0,@score:=null)B 
) C WHERE C.user_name ='lisi'

以上。

欢迎大家评论，有更好的方法的话，大家可以相互交流下。