select sum(t1.node_val) ddgsfqd1,
sum(t5.node_val) arsxhfqd1,
sum(t3.node_val) brsxhfqd1,
sum(t9.node_val) crsxhfqd1,
sum(t2.node_val) ddgsfqd2,
sum(t6.node_val) arsxhfqd2,
sum(t8.node_val) brsxhfqd2,
sum(t10.node_val) crsxhfqd2,
sum(t3.node_val) ddgsfqd3,
sum(t23.node_val) arsxhfyx1,
sum(t25.node_val) brsxhfyx1,
sum(t27.node_val) crsxhfyx1,
sum(t4.node_val) ddgsfqd4,
sum(t24.node_val) arsxhfyx2,
sum(t26.node_val) brsxhfyx2,
sum(t28.node_val) crsxhfyx2,
sum(t19.node_val) ddgsfyx1,
sum(t13.node_val) arsxhfhdl1,
sum(t15.node_val) brsxhfhdl1,
sum(t17.node_val) crsxhfhdl1,
sum(t20.node_val) ddgsfyx2,
sum(t14.node_val) arsxhfhdl2,
sum(t16.node_val) brsxhfhdl2,
sum(t18.node_val) crsxhfhdl2,
sum(t21.node_val) ddgsfyx3,
sum(t22.node_val) ddgsfyx4,
sum(t11.node_val) ddgsfhdl1,
sum(t12.node_val) ddgsfhdl2,
nvl(avg(t29.node_val), 0) ddgsfhdl3,
nvl(avg (t30.node_val), 0) ddgsfhdl4
from bi_months m,
node_data_h t1,
node_data_h t2,
node_data_h t3,
node_data_h t4,
node_data_h t5,
node_data_h t6,
node_data_h t7,
node_data_h t8,
node_data_h t9,
node_data_h t10,
node_data_h t11,
node_data_h t12,
node_data_h t13,
node_data_h t14,
node_data_h t15,
node_data_h t16,
node_data_h t17,
node_data_h t18,
(select node_id,to_char(data_time, 'yyyy-mm-dd') data_time, sum(node_val) node_val from node_data_h group by node_id, to_char(data_time, 'yyyy-mm-dd')) t19,
(selec node_id, to_char(data_time, 'yyyy-mm-dd') data_time,sum(node_val) node_val from node_data_h group by node_id, to_char(data_time, 'yyyy-mm-dd')) t20,
(select node_id, to_char(data_time, 'yyyy-mm-dd') data_time, sum(node_val) node_val from node_data_h group by node_id, to_char(data_time, 'yyyy-mm-dd')) t21,
(select node_id, to_char(data_time, 'yyyy-mm-dd') data_time, sum(node_val) node_val from node_data_h group by node_id, to_char(data_time, 'yyyy-mm-dd')) t22,
(select node_id,to_char(data_time, 'yyyy-mm-dd') data_time,sum(node_val) node_val from node_data_h group by node_id, to_char(data_time, 'yyyy-mm-dd')) t23,
(select node_id, to_char(data_time, 'yyyy-mm-dd') data_time, sum (node_val) node_val from node_data_h group by node_id, to_char(data_time, 'yyyy-mm-dd')) t24,
(select node_id,to_char(data_time, 'yyyy-mm-dd') data_time,sum(node_val) node_val from node_data_h group by node_id, to_char(data_time, 'yyyy-mm-dd')) t25,
(select node_id,to_char(data_time, 'yyyy-mm-dd') data_time,sum(node_val) node_val from node_data_h group by node_id, to_char(data_time, 'yyyy-mm-dd')) t26,
(select node_id,to_char(data_time, 'yyyy-mm-dd') data_time,sum(node_val) node_val from node_data_h group by node_id, to_char(data_time, 'yyyy-mm-dd')) t27,
(select node_id, to_char(data_time, 'yyyy-mm-dd') data_time, sum(node_val) node_val from node_data_h group by node_id, to_char(data_time, 'yyyy-mm-dd')) t28,
(select node_id,to_char(data_time, 'yyyy-mm-dd') data_time, avg(decode(node_val, 0, null, node_val)) node_val from node_data_h group by node_id, to_char(data_time, 'yyyy-mm-dd')) t29,
(select node_id,to_char(data_time, 'yyyy-mm-dd') data_time, avg(decode(node_val, 0, null, node_val)) node_val from node_data_h group by node_id, to_char(data_time, 'yyyy-mm-dd')) t30
where 1 = 1
and m.select_date >= to_date('2009-03-01', 'yyyy-mm-dd')
And m.select_date <= to_date('2009-03-31', 'yyyy-mm-dd')
And t1.node_id(+) = 'E637'
And t2.node_id(+) = 'E112'
And t3.node_id(+) = 'E1842'
And t4.node_id(+) = 'E1815'
And t5.node_id(+) = 'E616'
And t6.node_id(+) = 'E91'
And t7.node_id(+) = 'E617'
And t8.node_id(+) = 'E92'
And t9.node_id(+) = 'E618'
And t10.node_id(+) = 'E93'
And t11.node_id(+) = 'W6054'
And t12.node_id(+) = 'W6154'
And t13.node_id(+) = 'W6016'
And t14.node_id(+) = 'W6116'
And t15.node_id(+) = 'W6046'
And t16.node_id(+) = 'W6146'
And t17.node_id(+) = 'W6018'
And t18.node_id(+)= 'W6118'
And t19.node_id(+) = '637'
And t20.node_id(+) = '112'
And t21.node_id(+) = '1842'
And t22.node_id(+) = '1815'
And t23.node_id(+) = '616'
And t24.node_id(+) = '91'
Andt25.node_id(+) = '617'
And t26.node_id(+) = '92'
And t27.node_id(+) = '618'
And t28.node_id(+) = '93'
And t29.node_id(+) = '1949'
And t30.node_id(+) = '1966'
And t1.data_time (+) = m.select_date
And t2.data_time(+) = m.select_date
and t3.data_time(+) = m.select_date
and t4.data_time(+) = m.select_date
and t5.data_time(+) = m.select_date
andt6.data_time(+) = m.select_date
and t7.data_time(+) = m.select_date
and t8.data_time(+) = m.select_date
and t9.data_time(+) = m.select_date
and t10.data_time(+)= m.select_date
and t11.data_time(+) = m.select_date
and t12.data_time(+) = m.select_date
and t13.data_time(+) = m.select_date
and t14.data_time(+) = m.select_date
and t15.data_time(+) = m.select_date
and t16.data_time(+) = m.select_date
and t17.data_time(+) = m.select_date
and t18.data_time(+) = m.select_date
and t19.data_time(+) = to_char (m.select_date, 'yyyy-mm-dd')
and t20.data_time(+) = to_char(m.select_date, 'yyyy-mm-dd')
and t21.data_time(+) = to_char(m.select_date, 'yyyy-mm-dd')
and t22.data_time(+)= to_char(m.select_date, 'yyyy-mm-dd')
and t23.data_time(+) = to_char(m.select_date, 'yyyy-mm-dd')
and t24.data_time(+) = to_char(m.select_date, 'yyyy-mm-dd')
andt25.data_time(+) = to_char(m.select_date, 'yyyy-mm-dd')
and t26.data_time(+) = to_char(m.select_date, 'yyyy-mm-dd')
and t27.data_time(+) = to_char(m.select_date, 'yyyy-mm-dd')
and t28.data_time(+) = to_char(m.select_date, 'yyyy-mm-dd')
and t29.data_time(+) = to_char(m.select_date, 'yyyy-mm-dd')
and t30.data_time(+) = to_char(m.select_date,'yyyy-mm-dd')
因为同一张表相关联的次数多,加上还要把各个值都放在界面上显示,所以速度非常慢。希望各位大哥大姐帮忙小弟看看如何优化
sum(t5.node_val) arsxhfqd1,
sum(t3.node_val) brsxhfqd1,
sum(t9.node_val) crsxhfqd1,
sum(t2.node_val) ddgsfqd2,
sum(t6.node_val) arsxhfqd2,
sum(t8.node_val) brsxhfqd2,
sum(t10.node_val) crsxhfqd2,
sum(t3.node_val) ddgsfqd3,
sum(t23.node_val) arsxhfyx1,
sum(t25.node_val) brsxhfyx1,
sum(t27.node_val) crsxhfyx1,
sum(t4.node_val) ddgsfqd4,
sum(t24.node_val) arsxhfyx2,
sum(t26.node_val) brsxhfyx2,
sum(t28.node_val) crsxhfyx2,
sum(t19.node_val) ddgsfyx1,
sum(t13.node_val) arsxhfhdl1,
sum(t15.node_val) brsxhfhdl1,
sum(t17.node_val) crsxhfhdl1,
sum(t20.node_val) ddgsfyx2,
sum(t14.node_val) arsxhfhdl2,
sum(t16.node_val) brsxhfhdl2,
sum(t18.node_val) crsxhfhdl2,
sum(t21.node_val) ddgsfyx3,
sum(t22.node_val) ddgsfyx4,
sum(t11.node_val) ddgsfhdl1,
sum(t12.node_val) ddgsfhdl2,
nvl(avg(t29.node_val), 0) ddgsfhdl3,
nvl(avg (t30.node_val), 0) ddgsfhdl4
from bi_months m,
node_data_h t1,
node_data_h t2,
node_data_h t3,
node_data_h t4,
node_data_h t5,
node_data_h t6,
node_data_h t7,
node_data_h t8,
node_data_h t9,
node_data_h t10,
node_data_h t11,
node_data_h t12,
node_data_h t13,
node_data_h t14,
node_data_h t15,
node_data_h t16,
node_data_h t17,
node_data_h t18,
(select node_id,to_char(data_time, 'yyyy-mm-dd') data_time, sum(node_val) node_val from node_data_h group by node_id, to_char(data_time, 'yyyy-mm-dd')) t19,
(selec node_id, to_char(data_time, 'yyyy-mm-dd') data_time,sum(node_val) node_val from node_data_h group by node_id, to_char(data_time, 'yyyy-mm-dd')) t20,
(select node_id, to_char(data_time, 'yyyy-mm-dd') data_time, sum(node_val) node_val from node_data_h group by node_id, to_char(data_time, 'yyyy-mm-dd')) t21,
(select node_id, to_char(data_time, 'yyyy-mm-dd') data_time, sum(node_val) node_val from node_data_h group by node_id, to_char(data_time, 'yyyy-mm-dd')) t22,
(select node_id,to_char(data_time, 'yyyy-mm-dd') data_time,sum(node_val) node_val from node_data_h group by node_id, to_char(data_time, 'yyyy-mm-dd')) t23,
(select node_id, to_char(data_time, 'yyyy-mm-dd') data_time, sum (node_val) node_val from node_data_h group by node_id, to_char(data_time, 'yyyy-mm-dd')) t24,
(select node_id,to_char(data_time, 'yyyy-mm-dd') data_time,sum(node_val) node_val from node_data_h group by node_id, to_char(data_time, 'yyyy-mm-dd')) t25,
(select node_id,to_char(data_time, 'yyyy-mm-dd') data_time,sum(node_val) node_val from node_data_h group by node_id, to_char(data_time, 'yyyy-mm-dd')) t26,
(select node_id,to_char(data_time, 'yyyy-mm-dd') data_time,sum(node_val) node_val from node_data_h group by node_id, to_char(data_time, 'yyyy-mm-dd')) t27,
(select node_id, to_char(data_time, 'yyyy-mm-dd') data_time, sum(node_val) node_val from node_data_h group by node_id, to_char(data_time, 'yyyy-mm-dd')) t28,
(select node_id,to_char(data_time, 'yyyy-mm-dd') data_time, avg(decode(node_val, 0, null, node_val)) node_val from node_data_h group by node_id, to_char(data_time, 'yyyy-mm-dd')) t29,
(select node_id,to_char(data_time, 'yyyy-mm-dd') data_time, avg(decode(node_val, 0, null, node_val)) node_val from node_data_h group by node_id, to_char(data_time, 'yyyy-mm-dd')) t30
where 1 = 1
and m.select_date >= to_date('2009-03-01', 'yyyy-mm-dd')
And m.select_date <= to_date('2009-03-31', 'yyyy-mm-dd')
And t1.node_id(+) = 'E637'
And t2.node_id(+) = 'E112'
And t3.node_id(+) = 'E1842'
And t4.node_id(+) = 'E1815'
And t5.node_id(+) = 'E616'
And t6.node_id(+) = 'E91'
And t7.node_id(+) = 'E617'
And t8.node_id(+) = 'E92'
And t9.node_id(+) = 'E618'
And t10.node_id(+) = 'E93'
And t11.node_id(+) = 'W6054'
And t12.node_id(+) = 'W6154'
And t13.node_id(+) = 'W6016'
And t14.node_id(+) = 'W6116'
And t15.node_id(+) = 'W6046'
And t16.node_id(+) = 'W6146'
And t17.node_id(+) = 'W6018'
And t18.node_id(+)= 'W6118'
And t19.node_id(+) = '637'
And t20.node_id(+) = '112'
And t21.node_id(+) = '1842'
And t22.node_id(+) = '1815'
And t23.node_id(+) = '616'
And t24.node_id(+) = '91'
Andt25.node_id(+) = '617'
And t26.node_id(+) = '92'
And t27.node_id(+) = '618'
And t28.node_id(+) = '93'
And t29.node_id(+) = '1949'
And t30.node_id(+) = '1966'
And t1.data_time (+) = m.select_date
And t2.data_time(+) = m.select_date
and t3.data_time(+) = m.select_date
and t4.data_time(+) = m.select_date
and t5.data_time(+) = m.select_date
andt6.data_time(+) = m.select_date
and t7.data_time(+) = m.select_date
and t8.data_time(+) = m.select_date
and t9.data_time(+) = m.select_date
and t10.data_time(+)= m.select_date
and t11.data_time(+) = m.select_date
and t12.data_time(+) = m.select_date
and t13.data_time(+) = m.select_date
and t14.data_time(+) = m.select_date
and t15.data_time(+) = m.select_date
and t16.data_time(+) = m.select_date
and t17.data_time(+) = m.select_date
and t18.data_time(+) = m.select_date
and t19.data_time(+) = to_char (m.select_date, 'yyyy-mm-dd')
and t20.data_time(+) = to_char(m.select_date, 'yyyy-mm-dd')
and t21.data_time(+) = to_char(m.select_date, 'yyyy-mm-dd')
and t22.data_time(+)= to_char(m.select_date, 'yyyy-mm-dd')
and t23.data_time(+) = to_char(m.select_date, 'yyyy-mm-dd')
and t24.data_time(+) = to_char(m.select_date, 'yyyy-mm-dd')
andt25.data_time(+) = to_char(m.select_date, 'yyyy-mm-dd')
and t26.data_time(+) = to_char(m.select_date, 'yyyy-mm-dd')
and t27.data_time(+) = to_char(m.select_date, 'yyyy-mm-dd')
and t28.data_time(+) = to_char(m.select_date, 'yyyy-mm-dd')
and t29.data_time(+) = to_char(m.select_date, 'yyyy-mm-dd')
and t30.data_time(+) = to_char(m.select_date,'yyyy-mm-dd')
因为同一张表相关联的次数多,加上还要把各个值都放在界面上显示,所以速度非常慢。希望各位大哥大姐帮忙小弟看看如何优化
解决方案 »
- 将mysql中表course(cno,cname)集成数据到oracle,在执行接口时出现错误:缺少参数C1_CNO
- 求教一条SQL语句
- oracle数据库
- TO_CHAR 后为什么有空格
- 是用or快还是分开快点???
- 能否将oracle的CHARACTER SET改为Unicode,谢谢!!!
- 无法进入oracle enterprise manager console
- 关于计算时间的函数
- ★★救命啊,我不小心删除了表中的数据,如何恢复★★
- 急!请问mysql,oracle for linux是不是免费的?
- plsql里面如何用命令查看 当前用户以及表所在的表空间?
- sqlserver 2000 中怎样访问oracle的存储过程
(selec node_id, to_char(data_time, 'yyyy-mm-dd') data_time,sum(node_val) node_val from node_data_h group by node_id, to_char(data_time, 'yyyy-mm-dd')) t20,
(select node_id, to_char(data_time, 'yyyy-mm-dd') data_time, sum(node_val) node_val from node_data_h group by node_id, to_char(data_time, 'yyyy-mm-dd')) t21,
(select node_id, to_char(data_time, 'yyyy-mm-dd') data_time, sum(node_val) node_val from node_data_h group by node_id, to_char(data_time, 'yyyy-mm-dd')) t22,
(select node_id,to_char(data_time, 'yyyy-mm-dd') data_time,sum(node_val) node_val from node_data_h group by node_id, to_char(data_time, 'yyyy-mm-dd')) t23,
(select node_id, to_char(data_time, 'yyyy-mm-dd') data_time, sum (node_val) node_val from node_data_h group by node_id, to_char(data_time, 'yyyy-mm-dd')) t24,
(select node_id,to_char(data_time, 'yyyy-mm-dd') data_time,sum(node_val) node_val from node_data_h group by node_id, to_char(data_time, 'yyyy-mm-dd')) t25,
(select node_id,to_char(data_time, 'yyyy-mm-dd') data_time,sum(node_val) node_val from node_data_h group by node_id, to_char(data_time, 'yyyy-mm-dd')) t26,
(select node_id,to_char(data_time, 'yyyy-mm-dd') data_time,sum(node_val) node_val from node_data_h group by node_id, to_char(data_time, 'yyyy-mm-dd')) t27,
(select node_id, to_char(data_time, 'yyyy-mm-dd') data_time, sum(node_val) node_val from node_data_h group by node_id, to_char(data_time, 'yyyy-mm-dd')) t28,
(select node_id,to_char(data_time, 'yyyy-mm-dd') data_time, avg(decode(node_val, 0, null, node_val)) node_val from node_data_h group by node_id, to_char(data_time, 'yyyy-mm-dd')) t29,
(select node_id,to_char(data_time, 'yyyy-mm-dd') data_time, avg(decode(node_val, 0, null, node_val)) node_val from node_data_h group by node_id, to_char(data_time, 'yyyy-mm-dd')) t30 这部分是不是可以建成一个视图,因为每个的数据结构都一样,这样看着也方便点其它的等高人来看吧:~
select
sum(case when t.data_time(+)=m.select_date and t.node_id(+)='E637' then t.node_val end) ddgsfqd1,
sum(case when t.data_time(+)=m.select_date and t.node_id(+)='E616' then t.node_val end) arsxhfqd1,
...
sum(case when to_char(t.data_time, 'yyyy-mm-dd')=to_char (m.select_date, 'yyyy-mm-dd')
and t.node_id(+)='637' then t.node_val end) ddgsfyx1,
sum(case when to_char(t.data_time, 'yyyy-mm-dd')=to_char (m.select_date, 'yyyy-mm-dd')
and t.node_id(+)='112' then t.node_val end) ddgsfyx2,
...
nvl(avg(case when to_char(t.data_time, 'yyyy-mm-dd')=to_char (m.select_date, 'yyyy-mm-dd')
and t.node_id(+)='1949' then t.node_val end), 0) ddgsfhdl3,
nvl(avg (case when to_char(t.data_time, 'yyyy-mm-dd')=to_char (m.select_date, 'yyyy-mm-dd')
and t.node_id(+)='1966' then t.node_val end), 0) ddgsfhdl4
from node_data_h t, bi_months m
sum(decode(t.node_id, 'E637', t.node_value, 0)) as ddgsfqd1,
...,
...
from m,t,v --简写
where t.data_time(+) = m.select_date
and v.data_time(+) = to_char(m.select_date, 'yyyy-mm-dd')
--三表用两个左连接可能效果更好
http://blog.csdn.net/pathuang68/archive/2009/04/16/4084116.aspx
希望能对你有所帮助。
select sum(decode(t1.node_id,'E637',t1.node_val,0) ddgsfqd1,
sum(decode(t1.node_id,'E616',t1.node_val,0) arsxhfqd1,
sum(decode(t1.node_id,'E1842',t1.node_val,0) brsxhfqd1,
sum(decode(t1.node_id,'E618',t1.node_val,0) crsxhfqd1,
sum(decode(t1.node_id,'E112',t1.node_val,0) ddgsfqd2,
sum(decode(t1.node_id,'E91',t1.node_val,0) arsxhfqd2,
sum(decode(t1.node_id, 'E92',t1.node_val,0) brsxhfqd2,
sum(decode(t1.node_id,'E93',t1.node_val,0) crsxhfqd2,
sum(decode(t1.node_id,'E1842',t1.node_val,0) ddgsfqd3,
sum(decode(t1.node_id,'616',t1.node_val,0) arsxhfyx1,
sum(t25.node_val) brsxhfyx1,--其它的自己参照上面的改吧,太多了
sum(t27.node_val) crsxhfyx1,
sum(t4.node_val) ddgsfqd4,
sum(t24.node_val) arsxhfyx2,
sum(t26.node_val) brsxhfyx2,
sum(t28.node_val) crsxhfyx2,
sum(t19.node_val) ddgsfyx1,
sum(t13.node_val) arsxhfhdl1,
sum(t15.node_val) brsxhfhdl1,
sum(t17.node_val) crsxhfhdl1,
sum(t20.node_val) ddgsfyx2,
sum(t14.node_val) arsxhfhdl2,
sum(t16.node_val) brsxhfhdl2,
sum(t18.node_val) crsxhfhdl2,
sum(t21.node_val) ddgsfyx3,
sum(t22.node_val) ddgsfyx4,
sum(t11.node_val) ddgsfhdl1,
sum(t12.node_val) ddgsfhdl2,
nvl(avg(t29.node_val), 0) ddgsfhdl3,
nvl(avg (t30.node_val), 0) ddgsfhdl4
from bi_months m,
node_data_h t1,
(select node_id,to_char(data_time, 'yyyy-mm-dd') data_time, sum(node_val) node_val from node_data_h group by node_id, to_char(data_time, 'yyyy-mm-dd')) t19,
(select node_id,to_char(data_time, 'yyyy-mm-dd') data_time, avg(decode(node_val, 0, null, node_val)) node_val from node_data_h group by node_id, to_char(data_time, 'yyyy-mm-dd')) t30
where 1 = 1
and m.select_date >= to_date('2009-03-01', 'yyyy-mm-dd')
And m.select_date <= to_date('2009-03-31', 'yyyy-mm-dd')
And t1.node_id(+) = 'E637'
And t2.node_id(+) = 'E112'
And t3.node_id(+) = 'E1842'
And t4.node_id(+) = 'E1815'
And t5.node_id(+) = 'E616'
And t6.node_id(+) = 'E91'
And t7.node_id(+) = 'E617'
And t8.node_id(+) = 'E92'
And t9.node_id(+) = 'E618'
And t10.node_id(+) = 'E93'
And t11.node_id(+) = 'W6054'
And t12.node_id(+) = 'W6154'
And t13.node_id(+) = 'W6016'
And t14.node_id(+) = 'W6116'
And t15.node_id(+) = 'W6046'
And t16.node_id(+) = 'W6146'
And t17.node_id(+) = 'W6018'
And t18.node_id(+)= 'W6118'
And t19.node_id(+) = '637'
And t20.node_id(+) = '112'
And t21.node_id(+) = '1842'
And t22.node_id(+) = '1815'
And t23.node_id(+) = '616'
And t24.node_id(+) = '91'
Andt25.node_id(+) = '617'
And t26.node_id(+) = '92'
And t27.node_id(+) = '618'
And t28.node_id(+) = '93'
And t29.node_id(+) = '1949'
And t30.node_id(+) = '1966'
And t1.data_time (+) = m.select_date
and t19.data_time(+) = to_char (m.select_date, 'yyyy-mm-dd')
and t30.data_time(+) = to_char(m.select_date,'yyyy-mm-dd')
select sum(decode(t1.node_id,'E637',t1.node_val,0) ddgsfqd1,
sum(decode(t1.node_id,'E616',t1.node_val,0) arsxhfqd1,
sum(decode(t1.node_id,'E1842',t1.node_val,0) brsxhfqd1,
sum(decode(t1.node_id,'E618',t1.node_val,0) crsxhfqd1,
sum(decode(t1.node_id,'E112',t1.node_val,0) ddgsfqd2,
sum(decode(t1.node_id,'E91',t1.node_val,0) arsxhfqd2,
sum(decode(t1.node_id, 'E92',t1.node_val,0) brsxhfqd2,
sum(decode(t1.node_id,'E93',t1.node_val,0) crsxhfqd2,
sum(decode(t1.node_id,'E1842',t1.node_val,0) ddgsfqd3,
sum(decode(t1.node_id,'616',t1.node_val,0) arsxhfyx1,
sum(t25.node_val) brsxhfyx1,--其它的自己参照上面的改吧,太多了
sum(t27.node_val) crsxhfyx1,
sum(t4.node_val) ddgsfqd4,
sum(t24.node_val) arsxhfyx2,
sum(t26.node_val) brsxhfyx2,
sum(t28.node_val) crsxhfyx2,
sum(t19.node_val) ddgsfyx1,
sum(t13.node_val) arsxhfhdl1,
sum(t15.node_val) brsxhfhdl1,
sum(t17.node_val) crsxhfhdl1,
sum(t20.node_val) ddgsfyx2,
sum(t14.node_val) arsxhfhdl2,
sum(t16.node_val) brsxhfhdl2,
sum(t18.node_val) crsxhfhdl2,
sum(t21.node_val) ddgsfyx3,
sum(t22.node_val) ddgsfyx4,
sum(t11.node_val) ddgsfhdl1,
sum(t12.node_val) ddgsfhdl2,
nvl(avg(t29.node_val), 0) ddgsfhdl3,
nvl(avg (t30.node_val), 0) ddgsfhdl4
from bi_months m,
node_data_h t1,
(select node_id,to_char(data_time, 'yyyy-mm-dd') data_time, sum(node_val) node_val from node_data_h group by node_id, to_char(data_time, 'yyyy-mm-dd')) t19,
(select node_id,to_char(data_time, 'yyyy-mm-dd') data_time, avg(decode(node_val, 0, null, node_val)) node_val from node_data_h group by node_id, to_char(data_time, 'yyyy-mm-dd')) t30
where 1 = 1
and m.select_date >= to_date('2009-03-01', 'yyyy-mm-dd')
And m.select_date <= to_date('2009-03-31', 'yyyy-mm-dd')
And t1.data_time (+) = m.select_date
and t19.data_time(+) = to_char (m.select_date, 'yyyy-mm-dd')
and t30.data_time(+) = to_char (m.select_date, 'yyyy-mm-dd')
sum(decode(t1.node_id,' E637',t1.node_val,0)) ddgsfqd1,
sum(decode(t1.node_id, 'E112',t1.node_val,0)) arsxhfqd1,
sum(decode(t1.node_id, 'E1842',t1.node_val,0)) brsxhfqd1,
sum(decode(t1.node_id, 'E1815',t1.node_val,0)) crsxhfqd1,
sum(decode(t1.node_id, 'E616',t1.node_val,0)) ddgsfqd2,
sum(decode(t1.node_id, 'E91',t1.node_val,0)) arsxhfqd2,
sum(decode(t1.node_id,' E617',t1.node_val,0)) brsxhfqd2,
sum(decode(t1.node_id,' E92',t1.node_val,0)) crsxhfqd2,
sum(decode(t1.node_id,' E618',t1.node_val,0)) ddgsfqd3,
sum(decode(t1.node_id, 'E93',t1.node_val,0)) arsxhfyx1,
sum(decode(t1.node_id, 'W6054',t1.node_val,0)) brsxhfyx1,
sum(decode(t1.node_id,' W6154' ,t1.node_val,0)) crsxhfyx1,
sum(decode(t1.node_id, 'W6016',t1.node_val,0)) ddgsfqd4,
sum(decode(t1.node_id, 'W6116',t1.node_val,0)) arsxhfyx2,
sum(decode(t1.node_id, 'W6046',t1.node_val,0)) brsxhfyx2,
sum(decode(t1.node_id, 'W6146',t1.node_val,0)) crsxhfyx2,
sum(decode(t1.node_id, 'W6018',t1.node_val,0)) ddgsfyx1,
sum(decode(t1.node_id, 'W6118',t1.node_val,0)) arsxhfhdl1,
sum(decode(t1.node_id, '637',t1.node_val,0)) brsxhfhdl1,
sum(decode(t1.node_id,'112',t1.node_val,0)) crsxhfhdl1,
sum(decode(t1.node_id, '1842',t1.node_val,0)) ddgsfyx2,
sum(decode(t1.node_id,'1815',t1.node_val,0)) arsxhfhdl2,
sum(decode(t1.node_id,'616',t1.node_val,0)) brsxhfhdl2,
sum(decode(t1.node_id, '91',t1.node_val,0)) crsxhfhdl2,
sum(decode(t1.node_id,'617',t1.node_val,0)) ddgsfyx3,
sum(decode(t1.node_id, '92',t1.node_val,0)) ddgsfyx4,
sum(decode(t1.node_id, '618',t1.node_val,0)) ddgsfhdl1,
sum(decode(t1.node_id, '93',t1.node_val,0)) ddgsfhdl2,
nvl(decode(t1.node_id, '1949',t1.node_val,0)) ddgsfhdl3,
nvl(decode(t1.node_id, '1966',t1.node_val,0)) ddgsfhdl4,
sum(t1.node_val) ddgsfqd1,
sum(t1.node_val) arsxhfqd1,
sum(t1.node_val) brsxhfqd1,
sum(t1.node_val) crsxhfqd1,
sum(t1.node_val) ddgsfqd2,
sum(t1.node_val) arsxhfqd2,
sum(t1.node_val) brsxhfqd2,
sum(t1.node_val) crsxhfqd2,
sum(t1.node_val) ddgsfqd3,
sum(t19.node_val) arsxhfyx1,
sum(t19.node_val) brsxhfyx1,
sum(t19.node_val) crsxhfyx1,
sum(t1.node_val) ddgsfqd4,
sum(t19.node_val) arsxhfyx2,
sum(t19.node_val) brsxhfyx2,
sum(t19.node_val) crsxhfyx2,
sum(t19.node_val) ddgsfyx1,
sum(t1.node_val) arsxhfhdl1,
sum(t1.node_val) brsxhfhdl1,
sum(t1.node_val) crsxhfhdl1,
sum(t19.node_val) ddgsfyx2,
sum(t1.node_val) arsxhfhdl2,
sum(t1.node_val) brsxhfhdl2,
sum(t1.node_val) crsxhfhdl2,
sum(t19.node_val) ddgsfyx3,
sum(t19.node_val) ddgsfyx4,
sum(t1.node_val) ddgsfhdl1,
sum(t1.node_val) ddgsfhdl2,
nvl(avg(t30.node_val), 0) ddgsfhdl3,
nvl(avg (t30.node_val), 0) ddgsfhdl4
from bi_months m,
node_data_h t1,
(select node_id,to_char(data_time, 'yyyy-mm-dd') data_time, sum(node_val) node_val from node_data_h group by node_id, to_char(data_time, 'yyyy-mm-dd')) t19,
(select node_id,to_char(data_time, 'yyyy-mm-dd') data_time, avg(decode(node_val, 0, null, node_val)) node_val from node_data_h group by node_id, to_char(data_time,'yyyy-mm-dd')) t30
where 1 = 1
and m.select_date >= to_date('2009-03-01', 'yyyy-mm-dd')
And m.select_date <= to_date('2009-03-31', 'yyyy-mm-dd')
And t1.data_time (+) = m.select_date
and t19.data_time(+) = to_char (m.select_date, 'yyyy-mm-dd')
and t30.data_time(+) = to_char (m.select_date, 'yyyy-mm-dd')
请问是否是改成这个样子啊?小弟误性不高。希望各位高手指点