sql_轉摘_提取每行記錄多列的排名前3大值

with tb as
(select 1 id,12 a,13 b,14 c,15 d,16 e,17 f,18 g from dual union all
 select 2,22,23,24,25,26,27,18 from dual union all
 select 3,32,33,34,35,36,37,18 from dual union all
 select 4,42,43,44,45,46,47,18 from dual union all
 select 5,52,53,54,55,56,57,18 from dual union all
 select 6,62,63,64,65,66,67,18 from dual),
            tb2 as
(select id,a sd from tb union all
 select id,b from tb union all
 select id,c from tb union all
 select id,d from tb union all
 select id,e from tb union all
 select id,f from tb union all
 select id,g from tb)
 
 select a.*,
        b.最大值,
        b.第二大,
        b.第三大
 from tb a,
     (select id,
             max(decode(rn,1,sd)) 最大值,
             max(decode(rn,2,sd)) 第二大,
             max(decode(rn,3,sd)) 第三大
      from  (select id,
                    sd,
                    row_number() over(partition by id order by sd desc) rn
             from tb2)
     group by id) b
 where a.id=b.id

 

 

小結:

     1,行轉列，列轉行

     2,with語法的多重引用，也名巢狀

     3,分析函式與decode的聯合使用，威力無窮

      4,group by 妙用

      5,union all使用

      6,最外層where條件，主表與子表的關聯，很妙

      7,decode函式僅會顯示符合其條件的值，不符合條件顯示為null;

           然後合併為一行，採用max，這樣全顯示到一行記錄了，不會是多條記錄了