Sql排名和分组排名

浏览数：20 / 时间：2015年06月12日

在很多时候，都有排名这个功能，比如排行榜，并且还需要分页的功能，一般可以再select的时候按照某一字段 oorder by XX desc，这样limit 查找就可以得到排名信息，但是有时候是需要多表连接，或者是有一个随机查看，在页面上并不是按照排名升降序。这个时候就需要用SQL来实现排名。

先准备测试数据：

Table：

 

CREATE TABLE `test` (

  `Score` int(255) NOT NULL,

  `Name` varchar(255) NOT NULL,

  `Type` varchar(255) NOT NULL

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

 

-- ----------------------------

-- Records of test

-- ----------------------------

INSERT INTO `test` VALUES (‘79‘, ‘张三‘, ‘数学‘);

INSERT INTO `test` VALUES (‘69‘, ‘张三‘, ‘语文‘);

INSERT INTO `test` VALUES (‘57‘, ‘张三‘, ‘英语‘);

INSERT INTO `test` VALUES (‘81‘, ‘李四‘, ‘数学‘);

INSERT INTO `test` VALUES (‘45‘, ‘李四‘, ‘语文‘);

INSERT INTO `test` VALUES (‘84‘, ‘李四‘, ‘英语‘);

INSERT INTO `test` VALUES (‘15‘, ‘王五‘, ‘数学‘);

INSERT INTO `test` VALUES (‘47‘, ‘王五‘, ‘语文‘);

技术分享

如果要查询数学科目的排名，可以用以下sql语句：

select a.*,@a := @a+1 as rank  from test a,(select @a:=0) b where type=‘

数学 ‘ order by a.score desc;

结果如下：

技术分享

也就是使用a变量，按照order by的顺序递增。这个很好理解，但是如果有分组排序的需求呢?

例如在Test表中，要用一条sql查出数学、语文、英语三个科目各自的排名呢？

首先可以想到方法还是类似查询单科排名，先将所有记录按照科目、得分排序，Order by Type,Score。然后自定义变量递增，但是关键就在于需要判断科目的记录有多少条，也即自定义变量归零重新递增的临界点。

简单排名使用一个变量a，那么在分组排序中可以考虑使用两个变量，a、b

先执行如下sql

select a.* from test a order by type,score;

技术分享

现在加上排名，先使用一个变量看得到什么效果：

select a.*,(@a := @a + 1) as rank from test a,(select @a := 0) b order by

 type,score;

技术分享

现在排名并没有按照科目的不同重新计算，我们只要解决排名何时归0，就可以得到分组排序的结果了。

再加一个中间变量nowType试试？让这个中间变量nowType等于Type（科目），只要下一条记录与这个中间变量nowType相等，rank就加1，不相等的话，rank就归零，来看下这条SQL：

select a.*,@lastType := @nowType,@ nowType:= a.type,if(@lastType = @nowType,@rank

:= @rank + 1,@rank := 0) as rank from test a,(select @a := 0,@ nowType:= 0,@rank :=

0)      b order by type,score;

现在看来是不是比较简单了，lastType用于记录上一条记录的Type，nowType是当前记录的Type，只要nowType=lastType，就说明是同一科目，rank加+，反之，rank归零。

技术分享

但是我们看到同样是将rank初始化为0，为什么上面是从1开始，而这次是从0开始呢？再分析下sql，在第一条记录的时候，lastType=0.而nowType=数学，肯定不相等，所以输出0，rank从0开始，只要将rank初始化为1就好了。

 select a.*,@lastType := @temp,@temp := a.type,if(@lastType = @temp,@rank

:= @rank + 1,@rank := 1) as rank from test a,(select @a := 0,@temp := 0,@rank :=

0)      b order by type,score;