select * from test;+------+------+| a | b |" />
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=utf8
INSERT INTO `sam` VALUES (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;
注:
@x 為一變量,
X:=Y 將Y值賦給X
聲明:本網頁內容旨在傳播知識,若有侵權等問題請及時與本網聯系,我們將在第一時間刪除處理。TEL:177 7030 7066 E-MAIL:11247931@qq.com