高级查询之 over(partition by ... order by ... )

create table test (id number(2), name varchar2(10), salary number(6,2));
insert into test values (1,'Tom',120);
insert into test values (2,'Ellen',240);
insert into test values (2,'Joe',80);
insert into test values (3,'Andy',200);
insert into test values (3,'Kary',500);
insert into test values (3,'Erick',1300);
insert into test values (3,'Hou',40);
insert into test values (3,'Mary',200);
insert into test values (3,'Secooler',800);
commit;

 

select ID,
       name,
       salary,
       --LAG()和LEAD()统计函数可以在一次查询中取出同一字段的前N行的数据和后N行的值
       lag(salary,1) over(order by id) lag1_salary,
       lead(salary,2) over(order by id) lead2_salary,
       --last_value()和first_value()统计在一次查询中取出同一字段值最大或最小的数据
       last_value(name) over(partition by id order by salary rows between unbounded preceding and unbounded following) as highest_sal_name,
       last_value(salary) over(partition by id order by salary rows between unbounded preceding and unbounded following) as highest_sal,
       first_value(name) over(partition by id order by salary rows between unbounded preceding and unbounded following) as low_sal_name,
       first_value(salary) over(partition by id order by salary rows between unbounded preceding and unbounded following) as low_sal,
       row_number() over(partition by id order by salary desc )as rn,
       --rank()不连续排名 dense_rank()连续排名
       rank() over(partition by id order by salary desc ) as rank,
       dense_rank() over(partition by id order by salary desc ) as dense_rank
  from test
 order by ID, name;

统计函数sum(),max(),min(),avg(),count()也可与之搭配使用