各位大侠,帮忙看看这段SQL有错吗?~~~ 我弄不明白了!~~ 有不明白的地方可以问我谢谢了!
select 'N',
oma01,
' ',
' ',
'',
isa01,
oma23,
isa08t - nvl(sum(tc_oob09), 0),
isa08t * oma24 - nvl(sum(tc_oob10), 0),
oma00,
oma55,
oma54t
from (SELECT 'N',
oma01,
' ',
' ',
'',
isa01,
oma23,
isa08t,
isa08t * oma24,
oma00,
oma55,
oma54t
FROM omab_tmp, isa_file
where isa04 = oma01) a,
(select tc_oob16, nvl(sum(tc_oob09), 0), nvl(sum(tc_oob10), 0)
from tc_ooa_file, tc_oob_file
where tc_ooa01 = tc_oob01
and tc_ooaconf <> 'X'
group by tc_oob16) b
where a.isa01 = b.tc_oob16(+)
解决方案 »
- oracle处理数据和java处理数据的比较
- 求助,关于oracle数的数据导入问题!
- 请教!使用 execute immediate 'insert into tab1 values(:a)' using a 速度增么变慢了呢?
- 关于业务逻辑与数据库分离的问题。
- 此sql不用decode怎么写?
- 求教SQL,高手请指点一下
- 怎么把SELECT出来的数据全部用TXT格式到出来
- 怎么跟踪存储过程
- 请教高手:ORACLE有无将表或视图输出成以逗号隔开的文本文件或EXCEL文件的简便方法?急急急!等待。。。
- Oracle 9i服务器端可以在win2000专业版装吗
- 一个查询语句,请各位帮帮忙。
- 这个外键关联查询sql语句应该怎么写?
isa08t * oma24 - nvl(sum(tc_oob10), 0),
里面的nvl(sum(tc_oob09), 0),
和这个 nvl(sum(tc_oob10), 0)需要建立别名使用,不能直接用
a.oma01,
' ',
' ',
'',
a.isa01,
a.oma23,
a.isa08t - b.tc_oob09,
a.isa08toma24 - b.tc_oob10,
a.oma00,
a.oma55,
a.oma54t
from (SELECT
c.oma01 as oma01,
c.isa01 as isa01,
c.oma23 as oma23,
c.isa08t as isa08t,
c.isa08t * oma24 as isa08toma24,
c.oma00 as oma00,
c.oma55 as oma55,
c.oma54t as oma54t
FROM omab_tmp c , isa_file d
where c.isa04 = d.oma01) a, -- omab_tmp 和 isa_file 表结构 不清楚,不晓得这些字段是什么表的。
(select tc_oob16 as tc_oob16, nvl(sum(tc_oob09), 0) as tc_oob09, nvl(sum(tc_oob10), 0) as tc_oob10
from tc_ooa_file, tc_oob_file
where tc_ooa01 = tc_oob01
and tc_ooaconf <> 'X'
group by tc_oob16) b
where a.isa01 = b.tc_oob16(+)
2、isa08t - nvl(sum(tc_oob09), 0)等没试过,不知道这样写会不会有问题
如果可以,至少不建议这样写。可以改为:在内层先GROUP BY,并对需要SUM的执行计算,赋给别名试试如下:
select 'N',
oma01,
' ',
' ',
'',
isa01,
oma23,
isa08t - nvl(tc_oob09_sum, 0),
isa08t * oma24 - nvl(tc_oob10_sum, 0),
oma00,
oma55,
oma54t
from (SELECT oma01,isa01,oma23,isa08t,oma24,oma00,oma55,oma54t
FROM omab_tmp, isa_file
where isa04 = oma01) a,
(select tc_oob16, nvl(sum(tc_oob09), 0) tc_oob09_sum, nvl(sum(tc_oob10), 0) tc_oob10_sum
from tc_ooa_file, tc_oob_file
where tc_ooa01 = tc_oob01 and tc_ooaconf <> 'X'
group by tc_oob16) b
where a.isa01 = b.tc_oob16(+);
这两个列要取一个别名,在外层引用时,分别用别名应该就可以