這幾天在項目開發中需要用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 |
+------+------+------+------------------+
12 rows in set (0.00 sec) 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)

轉載于:https://blog.51cto.com/samyubw/232079

與50位技術專家面對面20年技術見證，附贈技術全景圖

總結

以上是生活随笔為你收集整理的MySQL中实现分组排序的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。