MySQL的rank函数实现_MySQL

Create Table: CREATE TABLE `sam` (  `a` int(11) DEFAULT NULL,  `b` int(11) DEFAULT NULL) ENGINE=MyISAM DEFAULT CHARSET=utf8

INSERT INTO `sam` VALUES (1,10),(1,15),(1,20),(1,25),(2,20),(2,22),(2,33),(2,45);

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 <br>4.结果：<br>+------+------+--------+------+<br>| a    | b    | rownum | rank |<br>+------+------+--------+------+<br>|    1 |   25 |      1 |    1 | <br>|    1 |   20 |      2 |    2 | <br>|    2 |   45 |      5 |    1 | <br>|    2 |   33 |      6 |    2 | <br>+------+------+--------+------+<br>4 rows in set (0.00 sec)<p>注：</p><p>@x 为一变量，<br>X:=Y 将Y值赋给X<br></p>