前段时间由于工作需要,要实现这样的一个功能:给的原始数据为多个大类,每个大类下面有很多小类,指标为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 |
| 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 |
+-----+-----+-------+
| 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 |
+----+-----+-----+-------+------+-------+
| 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 |
+----+-----+-----+-------+------+-------+
| 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 |
+-----+-----+-------+
| eid | cid | value |
+-----+-----+-------+
| 1 | 2 | 4 |
| 1 | 3 | 3 |
| 2 | 8 | 7 |
| 2 | 6 | 3 |
+-----+-----+-------+
也就是最终的结果。