测试表 test ;
ID W1 W2 W3
44 50 30 1
43 40 60 40
42 10 20 100
41 30 10 15
按照W1、W2、W3值的大小 取前TOP3，转换为一行取ID的值
期望结果如下一行（根据）：
W1ID1 W1ID2 W1ID3 H1ID1 H1ID2 H1ID3 L1ID1 L1ID2 L1ID3
44 43 41 43 44 42 42 43 41
转换
------解决方案--------------------
create table pm_test(id number,w1 number,w2 number,w3 number)
insert into pm_test values(44,50,30,1);
insert into pm_test values(43,40,60,40);
insert into pm_test values(42,10,20,100);
insert into pm_test values(41,30,10,15);
commit;
--查询语句如下：
select max(case when t1.rn=1 then t1.id end) w1d1,
max(case when t1.rn=2 then t1.id end) w1d2,
max(case when t1.rn=3 then t1.id end) w1d3,
max(case when t2.rn=1 then t2.id end) w2d1,
max(case when t2.rn=2 then t2.id end) w2d2,
max(case when t2.rn=3 then t2.id end) w2d3,
max(case when t3.rn=1 then t3.id end) w3d1,
max(case when t3.rn=2 then t3.id end) w3d2,
max(case when t3.rn=3 then t3.id end) w3d3
from
(
select id,row_number() over(order by w1 desc) rn from pm_test
) t1 ,
(
select id,row_number() over(order by w2 desc) rn from pm_test
) t2 ,
(
select id,row_number() over(order by w3 desc) rn from pm_test
) t3
where t1.rn<=3 and t2.rn<=3 and t3.rn<=3
and t1.rn=t2.rn and t2.rn=t3.rn