大家好,请教个问题。现在oracle中,有一张表,结构大概如下:
公司名,人名,。
A 甲1
A 甲2
.。。
A 甲178
A 甲179
B 乙1
B 乙2
.。。
B 乙98
B 乙99
C 丙1
C 丙2
.。。
C 丙211
C 丙212现在想每个公司,各取10个本公司的员工,请问怎么写这语句呢?多谢
公司名,人名,。
A 甲1
A 甲2
.。。
A 甲178
A 甲179
B 乙1
B 乙2
.。。
B 乙98
B 乙99
C 丙1
C 丙2
.。。
C 丙211
C 丙212现在想每个公司,各取10个本公司的员工,请问怎么写这语句呢?多谢
from (select t.*,row_number() over(partition by t.公司名 order by 1) rn
from t
)
where rn<=10
;
from (select t.*,rank() over(partition by t.公司名 order by t.人名) rn
from t
)
where rn<=10
with t1 as
(
select 'A' t_name,'甲1' p_name from dual
union all
select 'A' t_name,'甲2' p_name from dual
union all
select 'A' t_name,'甲3' p_name from dual
union all
select 'B' t_name,'乙1' p_name from dual
union all
select 'B' t_name,'乙3' p_name from dual
union all
select 'B' t_name,'乙22' p_name from dual
)select t_name,p_name
from
(
select t_name,p_name,
row_number() over(partition by t_name order by p_name) rn
from t1
)
where rn <= 2 t_name p_name
-------------------------
1 A 甲1
2 A 甲2
3 B 乙1
4 B 乙22
使用函数的时候 要先了解清楚函数 rank() over()是跳数排序 如果刚好第10个11个名称相同呢 那这样取出来的值 至少有11行记录了
with t1 as
(
select 'A' t_name,'甲1' p_name from dual
union all
select 'A' t_name,'甲2' p_name from dual
union all
select 'A' t_name,'甲2' p_name from dual
union all
select 'A' t_name,'甲2' p_name from dual
union all
select 'A' t_name,'甲3' p_name from dual
)select *
from (
select t_name,rank() over(partition by t_name order by p_name) rk
from t1)
where rk <= 2
t_name rk
--------------------
1 A 1
2 A 2
3 A 2
4 A 2
select empnum, dept, salary,
rank() over (partition by dept order by salary desc nulls last) as rank,
dense_rank() over (partition by dept order by salary desc nulls last)as denserank,
row_number() over (partition by dept order by salary desc nulls last)as rownumber
from emptab;EMPNUM DEPT SALARY RANK DENSERANK ROWNUMBER
------ ---- ------ ---- --------- ---------
6 1 78000 1 1 1
2 1 75000 2 2 2
7 1 75000 2 2 3
11 1 53000 4 3 4
5 1 52000 5 4 5
1 1 50000 6 5 6
--------------------------------------------------
9 2 51000 1 1 1
4 2 - 2 2 2