有两行数据如下:STAT_ITEM_NAME STAT_ITEM_CODE Y2006 Y2007 Y2008
总收入 A0103 127030.53 17622393.01 22080530.55
人次 B0101 2231 220193 242716想得到如下数据
STAT_ITEM_NAME STAT_ITEM_CODE Y2006 Y2007 Y2008
总收入 A0103 127030.53 17622393.01 22080530.55
人次 B0101 2231 220193 242716
平均收入 c01 90 80 100平均收入= 总收入/ 人次
总收入 A0103 127030.53 17622393.01 22080530.55
人次 B0101 2231 220193 242716想得到如下数据
STAT_ITEM_NAME STAT_ITEM_CODE Y2006 Y2007 Y2008
总收入 A0103 127030.53 17622393.01 22080530.55
人次 B0101 2231 220193 242716
平均收入 c01 90 80 100平均收入= 总收入/ 人次
(
select 'A0103' a, 127030.53 b, 17622393.01 c, 22080530.55 d from dual
union all
select 'B0101', 2231, 220193, 242716 from dual
)
select a,b,c,d from a
union all
select 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.55
2 B0101 2231 220193 242716
3 C01 57 80 91
(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 = '人次'