日期:2014-05-17 浏览次数:20838 次
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