mysql实现ORACLE 中ROWNUM函数抽象版

前段时间由于工作需要，要实现这样的一个功能：给的原始数据为多个大类，每个大类下面有很多小类，指标为value，现在需要根据value由大到小选出每个大类中小类的top2。但是刚开始却始终出现一个问题，就是第一次选出的数据都是全部，在执行第二次往后就正常了，后来研究了一下，终于找到这个的所在，现在拿出来和大家做一下分享。

原始数据为

eid  cid   value
1     1       1
1     2       4
1     3      3
1     4       1
2     5       2
2     6       3
2     7       1 

2     8       7

建表sql为 ：

create table testTable(

id int(32) not null primary key auto_increment,    

eid varchar(20) not null,

cid   varchar(20) not null,

value int(2) not null)

ENGINE= MYISAM CHARACTER SET utf8 ;

插入数据sql为：insert into testTable (eid,cid,value) values (1,1,1),(1,2,4),(1,3,3),(1,4,1),(2,5,2),(2,6,3),(2,7,1),(2,8,7);

第一次执行sql为：select t.eid,t.cid,t.value from (select *, @rank:=if(@eid = b.eid, @rank + 1, 1) as rank,(@eid:=b.eid) as bigId from (select * from testTable order by eid,value desc) as b) as t where t.rank<=2 ;执行结果为

+-----+-----+-------+
| eid | cid | value |
+-----+-----+-------+
| 1   | 2   |     4 |
| 1   | 3   |     3 |
| 1   | 1   |     1 |
| 1   | 4   |     1 |
| 2   | 8   |     7 |
| 2   | 6   |     3 |
| 2   | 5   |     2 |
| 2   | 7   |     1 |
+-----+-----+-------+

发现没有实现功能，在执行一次就成功了，执行结果为

+-----+-----+-------+
| eid | cid | value |
+-----+-----+-------+
| 1   | 2   |     4 |
| 1   | 3   |     3 |
| 2   | 8   |     7 |
| 2   | 6   |     3 |
+-----+-----+-------+

这是为什么呢？后来拆分这个语句终于找到原因了。

第一次执行select *, @rank:=if(@eid = b.eid, @rank + 1, 1) as rank,(@eid:=b.eid) as bigId from (select * from testTable order by eid,value desc) as b;的时候，执行结果为

+----+-----+-----+-------+------+-------+
| id | eid | cid | value | rank | bigId |
+----+-----+-----+-------+------+-------+
|  2 | 1   | 2   |     4 |    1 | 1     |
|  3 | 1   | 3   |     3 |    1 | 1     |
|  1 | 1   | 1   |     1 |    1 | 1     |
|  4 | 1   | 4   |     1 |    1 | 1     |
|  8 | 2   | 8   |     7 |    1 | 2     |
|  6 | 2   | 6   |     3 |    1 | 2     |
|  5 | 2   | 5   |     2 |    1 | 2     |
|  7 | 2   | 7   |     1 |    1 | 2     |
+----+-----+-----+-------+------+-------+

给每行添加的rank编号都是1，所以最后在选择小于2的时候就选了全部的数据。当在第二次执行这个sql的时候发现rank的编号就正常了

+----+-----+-----+-------+------+-------+
| id | eid | cid | value | rank | bigId |
+----+-----+-----+-------+------+-------+
|  2 | 1   | 2   |     4 |    1 | 1     |
|  3 | 1   | 3   |     3 |    2 | 1     |
|  1 | 1   | 1   |     1 |    3 | 1     |
|  4 | 1   | 4   |     1 |    4 | 1     |
|  8 | 2   | 8   |     7 |    1 | 2     |
|  6 | 2   | 6   |     3 |    2 | 2     |
|  5 | 2   | 5   |     2 |    3 | 2     |
|  7 | 2   | 7   |     1 |    4 | 2     |
+----+-----+-----+-------+------+-------+

，依次是每个大类下小类个数的编号，这个时候就会想到应该是初始化的问题，没有对eid，rank的值进行初始化，导致了在第一次执行的时候失败。于是对sql进行了修改如下:

新的sql为 select *, @rank:=if(@eid = b.eid, @rank + 1, 1) as rank,(@eid:=b.eid) as bigId from (select * from testTable order by eid,value desc) as b,(select @eid:= null, @rank := 0) as a; 相比于上面的sql他添加了(select @eid:= null, @rank := 0) as a，对这两个值进行了初始化，于是第一次执行的时候也就成功实现了需求。最后完整的sql为

select t.eid,t.cid,t.value from(select *, @rank:=if(@eid = b.eid, @rank + 1, 1) as rank,(@eid:=b.eid) as bigId from (select * from testTable order by eid,value desc) as b,(select @eid:= null, @rank := 0) as a) as t where t.rank<=2;执行一次就能得到如下结果

+-----+-----+-------+
| eid | cid | value |
+-----+-----+-------+
| 1   | 2   |     4 |
| 1   | 3   |     3 |
| 2   | 8   |     7 |
| 2   | 6   |     3 |
+-----+-----+-------+

也就是最终的结果。