有两行数据如下: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平均收入= 总收入/ 人次
解决方案 »
- 关于排序设定 (急)
- oracle10g登录问题
- ------------DBLINK无法连接----------------
- pl sql 半角的判断
- 利用sqlplus查询控制文件出现的问题
- 实在不会了 高手给详细写出sql代码吧 100分相送
- compute语句问题高手帮帮忙高分求解
- oracle如何执行*.sql脚本文件?
- 我的机器是C566,128M,跑oracle9还是oracle8?他们的区别是不是很大?
- oracle中怎么选出最前面的几条,类似于sqlserver中的top(n)语句
- oracle 10g 64位 透明网关
- 紧急问题,昨天我查询的数据库有100条记录,今天一查只有80条记录了。
(
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 = '人次'