Mysql 的rank 函数实现的问题
转自：http://www.blogjava.net/dunkbird/archive/2011/01/28/343718.html表特征：mysql> select * from test;+------+------+| a | b |+------+------+| 1 | 20 || 1 | 21 || 1 | 24 || 2 | 20 || 2 | 32 || 2 | 14 |+------+------+6 rows in set (0.00 sec)现在，我们以a分组，查询b列最大的2个值。 这条sql要怎么写了？1.创建表Create Table: CREATE TABLE `sam` ( `a` int(11) DEFAULT NULL, `b` int(11) DEFAULT NULL) ENGINE=MyISAM DEFAULT CHARSET=utf82.插入模拟数据INSERT INTO `sam` VALUES (1,10),(1,15),(1,20),(1,25),(2,20),(2,22),(2,33),(2,45);+------+------+| a | b |+------+------+| 1 | 10 || 1 | 15 || 1 | 20 || 1 | 25 || 2 | 20 || 2 | 22 || 2 | 33 || 2 | 45 |+------+------+3.SQL实现select a,b,rownum,rank from (select ff.a,ff.b,@rownum:=@rownum+1 rownum,if(@pa=ff.a,@rank:=@rank+1,@rank:=1) as rank,@pa:=ff.a FROM (select a,b from sam group by a,b order by a asc,b desc) ff,(select @rank:=0,@rownum:=0,@pa=null) tt) result having rank <=2;4.结果：+------+------+--------+------+| a | b | rownum | rank |+------+------+--------+------+| 1 | 25 | 1 | 1 || 1 | 20 | 2 | 2 || 2 | 45 | 5 | 1 || 2 | 33 | 6 | 2 |+------+------+--------+------+4 rows in set (0.00 sec)
