这几天在项目开发中需要用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');
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)
+------+------+------+
| 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)
+------+------+------+------------------+
| 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实现
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)
(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)