Mysql和Oracle组内排序的区别

Mysql和Oracle组内排序的区别

一、Oracle组内排序Eg：

1. 创建表：

drop table t_emp_wpzhang;
create table t_emp_wpzhang(
id int(11) primary key, 
name varchar(50),
age int(3),
dept varchar(20),
salary decimal(10 , 2)
);

2. 准备数据

insert all 
    into t_emp_wpzhang(id,name,age,dept,salary) values (1, 'zzz', 18 , '销售部', 6000 )
    into t_emp_wpzhang(id,name,age,dept,salary) values (2, 'www', 19 , '销售部' , 7000)
    into t_emp_wpzhang(id,name,age,dept,salary) values (3, 'ppp', 20 , '研发部', 8000)
    into t_emp_wpzhang(id,name,age,dept,salary) values (4, 'zwp', 21 , '财务部', 9000)
    into t_emp_wpzhang(id,name,age,dept,salary) values (5, 'zhangwuping', 22 , '研发部', 10000)
    into t_emp_wpzhang(id,name,age,dept,salary) values (9, 'wpzhang', 23 , '财务部', 11000)
    into t_emp_wpzhang(id,name,age,dept,salary) values (10, 'kelsey', 24 , '研发部', 12000)
    into t_emp_wpzhang(id,name,age,dept,salary) values (11, 'wp', 25 , '研发部', 13000)
select 1 from dual; 

3. 组内排序语法

select t.* ,
   ROW_NUMBER ()
       OVER (
                  PARTITION BY t.dept
                  ORDER BY t.age )
                  AS RN
from t_emp_wpzhang t 

4. 结果
   

5. 另外：

select * from (
 select t.* ,
   ROW_NUMBER ()
       OVER (
                  PARTITION BY t.dept
                  ORDER BY t.age )
                  AS RN
     from t_emp_wpzhang t
)where rn = 1;

可以取出每组的第一个
【注】在使用over等开窗函数时，over里头的分组及排序的执行晚于“where，group by，order by”的执行。

二、Mysql中组内排序
1.创建表：

drop table t_emp_wpzhang;
create table t_emp_wpzhang(
id int(11) primary key auto_increment , 
name varchar(50),
age int(3),
dept varchar(20),
salary decimal(10 , 2)
);

2.准备数据

insert into t_emp_wpzhang(id,name,age,dept,salary) values 
(1, 'zzz', 18 , '销售部', 6000 ),
(2, 'www', 19 , '销售部' , 7000),
(3, 'ppp', 20 , '研发部', 8000),
(4, 'zwp', 21 , '财务部', 9000),
(5, 'zhangwuping', 22 , '研发部', 10000),
(9, 'wpzhang', 23 , '财务部', 11000),
(10, 'kelsey', 24 , '研发部', 12000),
(11, 'wp', 25 , '研发部', 13000);

3. 组内排序

select @rownum:=@rownum+1 rownum,a.* ,
if(@dept=a.dept or (@dept is null and a.dept is null),
if(@age=a.age or (@age is null and a.age is null),@rank,@rank:=@rank+1),
           @rank:=1
) as row_number,
@dept:=a.dept,
@age:=a.age
from(SELECT * from t_emp_wpzhang order by dept,age asc)a,
 (select @rownum :=0,@dept:=null,@age:=null,@rank:=1)b

【说明】
1) 遍历表a 的第一行：变量@dept=null，@age=null => @rank=1 => @dept=‘研发部’ @age=‘20’
2) 遍历表的第二行：第一个if判断第二行的dept和@dept相等，则进行第二个if判断第二行的age和@age是否相等，相等row_number=@rank，不相等row_number=@rank+1;第一个if判断第二行的dept和@dept不相等，则row_num=1
3) 表第二行的dept值赋予给@dept 第二行的@age赋予给@age
依次遍历表的第三行，第四行…