Oracle Partition By
时间：2021-07-01 10:21:17
帮助过：7人阅读
Parttion by 关键字是Oracle中分析性函数的一部分，
它和聚合函数不同的地方在于它能够返回一个分组中的多条记录，
而聚合函数一般只有一条反映统计值的结果。
*/
--示例查询出每个部门工资最低的员工编号【每个部门可能有多个最低的工资员工】
create table tsaler(
userid varchar(
10),
salary number,
deptid varchar(
1)
);
insert into tsaler(userid, salary, deptid)
values(
‘1‘,
‘200‘,
‘1‘);
insert into tsaler(userid, salary, deptid)
values(
‘2‘,
‘2000‘,
‘1‘);
insert into tsaler(userid, salary, deptid)
values(
‘3‘,
‘200‘,
‘1‘);
insert into tsaler(userid, salary, deptid)
values(
‘4‘,
‘1000‘,
‘2‘);
insert into tsaler(userid, salary, deptid)
values(
‘5‘,
‘1000‘,
‘2‘);
insert into tsaler(userid, salary, deptid)
values(
‘6‘,
‘3000‘,
‘2‘);
commit;
--方法一
select tsaler.
*
from tsaler
inner join (
select min(salary)
as salary, deptid
from tsaler
group by deptid) c
on tsaler.salary
= c.salary
and tsaler.deptid
= c.deptid;
--方法二
select *
from tsaler
inner join (
select min(salary)
as salary, deptid
from tsaler
group by deptid) c
using (salary, deptid);
--方法三
--row_number() 顺序排序
select row_number()
over(partition
by deptid
order by salary) my_rank ,deptid,USERID,salary
from tsaler;
--rank() （跳跃排序，如果有两个第一级别时，接下来是第三级别）
select rank()
over(partition
by deptid
order by salary) my_rank,deptid,USERID,salary
from tsaler;
--dense_rank（）（连续排序，如果有两个第一级别时，接下来是第二级）
select dense_rank()
over(partition
by deptid
order by salary) my_rank,deptid,USERID,salary
from tsaler;
--①
select *
from (
select rank()
over(partition
by deptid
order by salary) my_rank,
deptid,
USERID,
salary
from tsaler)
where my_rank
= 1;
--②
select *
from (
select dense_rank()
over(partition
by deptid
order by salary) my_rank,
deptid,
USERID,
salary
from tsaler)
where my_rank
= 1;
Oracle Partition By
标签：记录 from ble 不同的 oracle 返回 pre color user
