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，这样全显示到一行记录了，不会是多条记录了

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/9240380/viewspace-682055/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/9240380/viewspace-682055/