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
解决方案 »
- 字符串连接(简单)
- 十个dmp文件如何导入到一个数据库中
- 有什么工具可以反向生成er图?
- VB中的SQL語句-錯誤在那裡?請教....等待中
- 谁帮我看看这个作业?
- 帮忙!!!acess数据库查询语句,转换为oracle数据库查询语句!在线等!
- 我在sql*plus 中执行sql语句建表时 报错ERROR 位于第 1 行: ORA-18008: 无法找到 OUTLN 方案
- 高分求oracle9i存储过程的教程,或例子
- 怎样去掉Oracle数据库中姓名字段中的空格,可能有一个空格,也可能有多个
- 谁知道Toad的注册码?定谢!
- 求助,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')用这句看看!