oracle关联统计查询
Typeidtypename
1工业
2商业
3农业
...
IDTypeidTimeValue
1110:00123
1210:00321
1310:00213
想要的结果：
Time工业农业商业 ...
10:00123213321 ...
...
...
...
数据量应该是蛮大的
至少是50万条吧
5分钟执行一次查询，兼顾性能！
求大神指导 oracle关联统计查询
[解决办法]
select m.time, sum(decode(m.typename,'工业',m.value,null)) '工业',
sum(decode(m.typename,'农业',m.value,null)) '农业',
sum(decode(m.typename,'商业',m.value,null)) '商业'
from
(SELECT b.ID, a.typename, b.TIME, b.VALUE
FROM a, b
WHERE a.typeid = b.typeid) m
group by m.time
order by m.time
[解决办法]
with t as
(select b.time,a.typename,sum(b.value) value from b left join a
on b.typeid=a.typeid
group by b.time,a.typename)
select * from t pivot (sum(value) for typename in ('工业','农业','商业'));
