select '本月与上月相比' sjd,a.pnt_name, nvl(a.value, 0)-nvl(b.value, 0) as value
from (select aa.sjd,aa.sort || '-' || substr(aa.pnt_name, 10, 10) as pnt_name,bb.value
from (select a.pnt_name,a.pnt_desc,'10' as sjd,a.report_name,a.sort
from sis_alghis_cfg a
where a.report_name = '汽机经济分析工作表5'
and a.is_used = 1) aa
left join (select max(a.pnt_name) as pnt_name,
max(a.pnt_desc) as pnt_desc,
avg(value) as value,
to_char(max(a.histime), 'mm') as histime
from report_day_data a
where to_char(a.histime, 'yyyy-mm') ='2009'||'-'||'10'
group by pnt_name) bb
on aa.sjd = bb.histime and aa.pnt_name = bb.pnt_name) a
left join (select aa.sjd,aa.sort || '-' || substr(aa.pnt_name, 10, 10) as pnt_name,bb.value
from (select a.pnt_name,a.pnt_desc,'99' as sjd,a.report_name,a.sort
from sis_alghis_cfg a
where a.report_name = '汽机经济分析工作表5'
and a.is_used = 1) aa
left join (select max(a.pnt_name) as pnt_name,
max(a.pnt_desc) as pnt_desc,
avg(value) as value,
to_char(max(a.histime), 'mm') as histime
from report_day_data a
where to_char(a.histime, 'yyyy-mm') ='2009'||'-'||'09'
group by pnt_name) bb
on aa.sjd = bb.histime and aa.pnt_name = bb.pnt_name) b
on a.pnt_name =b.pnt_name
from (select aa.sjd,aa.sort || '-' || substr(aa.pnt_name, 10, 10) as pnt_name,bb.value
from (select a.pnt_name,a.pnt_desc,'10' as sjd,a.report_name,a.sort
from sis_alghis_cfg a
where a.report_name = '汽机经济分析工作表5'
and a.is_used = 1) aa
left join (select max(a.pnt_name) as pnt_name,
max(a.pnt_desc) as pnt_desc,
avg(value) as value,
to_char(max(a.histime), 'mm') as histime
from report_day_data a
where to_char(a.histime, 'yyyy-mm') ='2009'||'-'||'10'
group by pnt_name) bb
on aa.sjd = bb.histime and aa.pnt_name = bb.pnt_name) a
left join (select aa.sjd,aa.sort || '-' || substr(aa.pnt_name, 10, 10) as pnt_name,bb.value
from (select a.pnt_name,a.pnt_desc,'99' as sjd,a.report_name,a.sort
from sis_alghis_cfg a
where a.report_name = '汽机经济分析工作表5'
and a.is_used = 1) aa
left join (select max(a.pnt_name) as pnt_name,
max(a.pnt_desc) as pnt_desc,
avg(value) as value,
to_char(max(a.histime), 'mm') as histime
from report_day_data a
where to_char(a.histime, 'yyyy-mm') ='2009'||'-'||'09'
group by pnt_name) bb
on aa.sjd = bb.histime and aa.pnt_name = bb.pnt_name) b
on a.pnt_name =b.pnt_name
解决方案 »
- 操作oracle的java程序,windows xp上正常运行, linux上出错
- 用pb导入数据问题
- 关于快速查询图形数据的Oracle数据库设计的请教
- oracle10g,有一个表和一个存储过程被锁了,但在v$locked_object中找不到记录,请问如何解决?
- 如何将2个整型字段相加。在线等~~急~~~
- 关于触发器问题,在线等.急.
- 谁有p3006854_9204_LINUX.zip的pach
- 为什么在win2000下装了oracle9i后用system,manager无法进入?
- Oracle 8i R2 (8.1.6) for NT 企业版在windows 2000 advanced server下的安装?
- 一个概念问题
- 求助,oracel中关于model字句的用法。
- [Forms]Oracle Forms排序分頁的問題(order by的位置與動態游標)(俺結帖率很高滴)
select aa.sjd,aa.sort || '-' || substr(aa.pnt_name, 10, 10) as pnt_name,bb.value
from (select a.pnt_name,a.pnt_desc,'10' as sjd,a.report_name,a.sort
from sis_alghis_cfg a
where a.report_name = '汽机经济分析工作表5'
and a.is_used = 1) aa
left join (select max(a.pnt_name) as pnt_name,
max(a.pnt_desc) as pnt_desc,
avg(value) as value,
to_char(max(a.histime), 'mm') as histime
from report_day_data a
where to_char(a.histime, 'yyyy-mm') ='2009'||'-'||'10'
group by pnt_name) bb
on aa.sjd = bb.histime and aa.pnt_name = bb.pnt_name
max(a.pnt_desc) as pnt_desc,
avg(value) as value,
to_char(max(a.histime), 'mm') as histime
from report_day_data a
where to_char(a.histime, 'yyyy-mm') ='2009'||'-'||'10'
group by pnt_name这句select max(a.pnt_name) as pnt_name,
max(a.pnt_desc) as pnt_desc,
avg(value) as value,
to_char(max(a.histime), 'mm') as histime
from report_day_data a
where to_char(a.histime, 'yyyy-mm') ='2009'||'-'||'09'
group by pnt_name有问题
max(a.pnt_desc) as pnt_desc,
avg(value) as value,
to_char(max(a.histime), 'mm') as histime
from report_day_data a
where to_char(a.histime, 'yyyy-mm') ='2009'||'-'||'09'
group by to_char(max(a.histime), 'mm')用这句看看!