mysql分组排序方案

mssql,oracle中

test表：

1 5 abc
2 6 bcd
1 7 ade
2 8 adc

 

select   a,b,c
from(
select   a,b,c
,row_number()over(partition   by   a   order   by   b   desc)   rn
from   test
)  
where   rn=1

 

由于MYSQL没有提供类似ORACLE中OVER()这样丰富的分析函数. 所以在MYSQL里需要实现这样的功能,我们只能用一些灵活的办法:

例1

1.首先我们来创建实例数据:

drop table if exists heyf_t10;
create table heyf_t10 (empid int ,deptid int ,salary decimal(10,2) );

insert into heyf_t10 values
(1,10,5500.00),
(2,10,4500.00),
(3,20,1900.00),
(4,20,4800.00),
(5,40,6500.00),
(6,40,14500.00),
(7,40,44500.00),
(8,50,6500.00),
(9,50,7500.00);

2. 确定需求: 根据部门来分组,显示各员工在部门里按薪水排名名次.

显示结果预期如下:

+-------+--------+----------+------+
| empid | deptid | salary | rank |
+-------+--------+----------+------+
| 1 | 10 | 5500.00 | 1 |
| 2 | 10 | 4500.00 | 2 |
| 4 | 20 | 4800.00 | 1 |
| 3 | 20 | 1900.00 | 2 |
| 7 | 40 | 44500.00 | 1 |
| 6 | 40 | 14500.00 | 2 |
| 5 | 40 | 6500.00 | 3 |
| 9 | 50 | 7500.00 | 1 |
| 8 | 50 | 6500.00 | 2 |
+-------+--------+----------+------+

 

3. SQL 实现

select empid,deptid,salary,rank from (
select heyf_tmp.empid,heyf_tmp.deptid,heyf_tmp.salary,@rownum:=@rownum+1 ,
if(@pdept=heyf_tmp.deptid,@rank:=@rank+1,@rank:=1) as rank,
@pdept:=heyf_tmp.deptid
from (
select empid,deptid,salary from heyf_t10 order by deptid asc ,salary desc
) heyf_tmp ,(select @rownum :=0 , @pdept := null ,@rank:=0) a ) result
;

4. 结果演示

mysql> select empid,deptid,salary,rank from (
-> select heyf_tmp.empid,heyf_tmp.deptid,heyf_tmp.salary,@rownum:=@rownum+1 ,
-> if(@pdept=heyf_tmp.deptid,@rank:=@rank+1,@rank:=1) as rank,
-> @pdept:=heyf_tmp.deptid
-> from (
-> select empid,deptid,salary from heyf_t10 order by deptid asc ,salary desc
-> ) heyf_tmp ,(select @rownum :=0 , @pdept := null ,@rank:=0) a ) result
-> ;
+-------+--------+----------+------+
| empid | deptid | salary | rank |
+-------+--------+----------+------+
| 1 | 10 | 5500.00 | 1 |
| 2 | 10 | 4500.00 | 2 |
| 4 | 20 | 4800.00 | 1 |
| 3 | 20 | 1900.00 | 2 |
| 7 | 40 | 44500.00 | 1 |
| 6 | 40 | 14500.00 | 2 |
| 5 | 40 | 6500.00 | 3 |
| 9 | 50 | 7500.00 | 1 |
| 8 | 50 | 6500.00 | 2 |
+-------+--------+----------+------+
9 rows in set (0.00 sec)

 

例2

 

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)