列转行sql

工资表 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 可返回多行。