17,086
社区成员
发帖
与我相关
我的任务
分享
存在一个雇员employee 表,创建该表的语句如下:
create table employee(emp_id number(3), emp_name varchar2(20), salary number(10,2));
该表的查询结果如下:
select emp_id, emp_name, salary from employee;
emp_id emp_name salary
-------- ------------------------------------------------------------------------------------
1 关羽 4632
2 张飞 4632
3 刘备 4932
4 赵云 5832
5 黄忠 4932
6 马超 5832
7 孔明 6632
8 姜维 4932
9 吕布 6432
请创建一个PL/SQL 存储过程,它确定employee表中薪水排名在前5个名次的雇员,输出显示这些雇员的名次,姓名,薪水。如果有雇员薪水相同,它们显示在同一行,用分号分隔; 如果某个名次的雇员为n(n大于等于2)个, 则下n-1个名次的雇员空缺。 显示结果示例如下:
1.孔明, 6632
2.吕布, 6432
3.赵云, 5832; 马超, 5832
4.
5.刘备, 4932; 黄忠, 4932; 姜维, 4932
--1.创建测试表:
create table tmp(emp_id number(3), emp_name varchar2(20), salary number(10,2));
insert into tmp values(1,'关羽',4632);
insert into tmp values(2,'张飞',4632);
insert into tmp values(3,'刘备',4932);
insert into tmp values(4,'赵云',5832);
insert into tmp values(5,'黄忠',4932);
insert into tmp values(6,'马超',5832);
insert into tmp values(7,'孔明',6632);
insert into tmp values(8,'姜维',4932);
insert into tmp values(9,'吕布',6432);
--2.SQL实现:
--方法1:
select replace(wmsys.wm_concat(ls), ',', ';'),rn
from(select emp_name ||','||salary ls,rn
from(select emp_id,emp_name,salary,rank() over(order by salary desc) rn from tmp)
);
--方法2:
select emp_id, max(substr(sys_connect_by_path(col, ';'), 2)) str
from (select emp_id,emp_name||','||salary as col,row_number()over(partition by emp_id order by emp_name) rn
from (select emp_name,salary,dense_rank() over( order by salary desc) as emp_id
from emp
)
where emp_id < 6
)
start with rn = 1
connect by rn = prior rn + 1
and emp_id = prior emp_id
group by emp_id;