工资表 t_salary(部门编号 depId, 人员编号personId, 工资 salary)
请用一个sql写出 每个部门的工资最大的三个数。要求如下格式输出!
部门编号 工资1 工资2 工资3
0001 8000 7000 6500
0002 10000 9000 4000
sql:
create table t_salary(depId char(4), personId char(8), salary float);insert into t_salary values ('0001','1221',10000);
insert into t_salary values ('0001','2334',9000);
insert into t_salary values ('0001','5343',8000);
insert into t_salary values ('0001','54454',8000);
insert into t_salary values ('0001','767676',9000);
insert into t_salary values ('0002','232343',7000);
insert into t_salary values ('0002','13411',6000);
insert into t_salary values ('0002','114411',2000);
insert into t_salary values ('0002','113211',5500);
insert into t_salary values ('0003','112311',7800);
insert into t_salary values ('0003','111441',4500);
insert into t_salary values ('0003','121411',4500);
select
sal.depId,
max(decode(sal.top, 1, sal.salary,0)) sal1,
max(decode(sal.top, 2, sal.salary,0)) sal2,
max(decode(sal.top, 3, sal.salary,0)) sal3
from
(select (temp.top-(select count(0) from t_salary s where s.depId<temp.depId)) top
, temp.depId, temp.salary
from (select rownum top, depId, salary
from t_salary
order by depId asc, salary desc) temp) sal
group by sal.depId
1 0001 9900 10000 11000
2 0002 7000 6000 2000
3 0003 7800 4500 4500
问题看上去基本解决,但是细细看看结果好像还是不太对 2 0002 7000 6000 5500,这次的主要原因处在 rownum。执行
select rownum top, depId, salary
from t_salary
order by depId asc, salary desc
奇怪的发现rownum既然和行号不一样。 结果如下:
top depId salary
1 3 0001 11000
2 2 0001 10000
3 1 0001 9900
4 4 0001 9000
5 7 0001 9000
6 6 0001 8000
7 5 0001 8000
8 8 0002 7000
9 9 0002 6000
10 11 0002 5500
11 10 0002 2000
12 12 0003 7800
13 13 0003 4500
14 14 0003 4500
下面就研究 一下rownum的问题吧!因为rownum是在 数据集合产生时就已经产生序号了,他和数据插入的顺序有关。
所以应该改为
select
sal.depId,
max(decode(sal.top, 1, sal.salary, 0)) sal1,
max(decode(sal.top, 2, sal.salary, 0)) sal2,
max(decode(sal.top, 3, sal.salary, 0)) sal3
from (select (temp.top -
(select count(0) from t_salary s where s.depId < temp.depId)) top,
temp.depId,
temp.salary
from (select rownum top, depId, salary
from (select * from t_salary order by depId asc, salary desc)) temp) sal
group by sal.depId
以上运行 结果:
1 0001 11000 10000 9900
2 0002 7000 6000 5500
3 0003 7800 4500 4500
呵呵 终于解决了!
下面阐述一下另一种做法
select tb.depId, max(tb.salary) salary1, max(decode(tb.t, 2,tb.salary)) salary2, min(tb.salary) salary3
from (select e.salary, e.depId, e.t
from (select depId,
personId,
salary,
row_number() over(partition by depId order by salary desc) t
from t_salary) e
where e.t <= 3) tb
group by tb.depId
首先partition by是分区组函数,他和group by的区别是group by 仅用于返回单行记录,而partition by 可返回多行。