這幾天在項目開發中需要用SQL實作資料的分組排序,以前在oracle的存儲過程開發中用過rank函數可以很友善的實作,但是現在在MySQL中是沒有類似的,經過上網搜尋資料現将MySQL中的資料排序和分組排序總結了一下,以備後查。
1.實作oracle中的rownum功能,将MySQL中的記錄進行順序排名。
步驟:
(1)建表,并插入模拟資料。
create table B(c1 int,c2 int,c3 varchar(10));
insert into B values(1,1,'a1'),(1,2,'a2'),(1,3,'a3'),(1,4,'a4');
insert into B values(2,1,'b1'),(2,2,'b2'),(2,3,'b3'),(2,4,'b4');
insert into B values(3,1,'c1'),(3,2,'c2'),(3,3,'c3'),(3,4,'c4');
mysql> select * from B;
+------+------+------+
| c1 | c2 | c3 |
| 1 | 1 | a1 |
| 1 | 2 | a2 |
| 1 | 3 | a3 |
| 1 | 4 | a4 |
| 2 | 1 | b1 |
| 2 | 2 | b2 |
| 2 | 3 | b3 |
| 2 | 4 | b4 |
| 3 | 1 | c1 |
| 3 | 2 | c2 |
| 3 | 3 | c3 |
| 3 | 4 | c4 |
12 rows in set (0.00 sec)
(2)SQL實作:
mysql> select c1,c2,c3,@order:=@order+1 rownum from B,(select @order:=0) tt;
+------+------+------+------------------+
| c1 | c2 | c3 | rownum |
| 1 | 1 | a1 | 1 |
| 1 | 2 | a2 | 2 |
| 1 | 3 | a3 | 3 |
| 1 | 4 | a4 | 4 |
| 2 | 1 | b1 | 5 |
| 2 | 2 | b2 | 6 |
| 2 | 3 | b3 | 7 |
| 2 | 4 | b4 | 8 |
| 3 | 1 | c1 | 9 |
| 3 | 2 | c2 | 10 |
| 3 | 3 | c3 | 11 |
| 3 | 4 | c4 | 12 |
2.實作分組排名
1.建立表
CREATE TABLE `sam` (
`a` int(11) DEFAULT NULL,
`b` int(11) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8
2.插入模拟資料
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實作
初始化使用者變量: set @pa=0;
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)
本文轉自 yubowei 51CTO部落格,原文連結:http://blog.51cto.com/samyubw/232079