oracle sql 如实现行间计算解决思路

with a as (select 'A0103' a, 127030.53 b, 17622393.01 c, 22080530.55 d  from dualunion allselect 'B0101', 2231, 220193, 242716 from dual)select a,b,c,d from aunion allselect a,round(sum(b1)/sum(b2)),round(sum(c1)/sum(c2)),round(sum(d1)/sum(d2))from  (select 'C01' a,sum(case when rn=1 then b else 0 end) b1          ,sum(case when rn=2 then b else 0 end)b2           ,sum(case when rn=1 then c else 0 end)c1          ,sum(case when rn=2 then c else 0 end)c2          ,sum(case when rn=1 then d else 0 end)d1          ,sum(case when rn=2 then d else 0 end)d2  from       (   select a,b,c,d,rownum rn from a )      group by a)group by a--result:1    A0103    127030.53    17622393.01    22080530.552    B0101    2231    220193    2427163    C01    57    80    91
------解决方案--------------------
create table test_yixl1 as
(select '总收入' as stat_item_name, 'A0103' as stat_item_code,  '127030.53' as  y2006,  
       '17622393.01' as y2007, '22080530.55' as y2008 from dual
union
select  '人次' as stat_item_name, 'b0101' as stat_item_code,  '2231' as y2006,  
      '220193' as y2007, '242716' as y2008 from dual);

select rownum rn , stat_item_name, stat_item_code,  
      y2006, y2007, y2008 from test_yixl1
union
select rownum+2 rn , '平均收入' as stat_item_name,  
      'c01'    as stat_item_code,  
      to_char(total.y2006/ave.y2006, 'fm999999990.90') as y2006,
      to_char(total.y2007/ave.y2007, 'fm999999990.90') as y2007,      
      to_char(total.y2008/ave.y2008, 'fm999999990.90') as y2008      
      from test_yixl1 total, test_yixl1 ave
where total.stat_item_name = '总收入' and ave.stat_item_name = '人次'