这是整个的sql语句。
a:
select m.jig_code, NVL(n.line, 0.0) as line, n.div_code, NVL(n.netjph, 0.0) as netjph, NVL(n.att, 0.0) as att
from (select CODE_KEY3 as jig_code
from pro_t_a05
where CODE_GRP = 'CONVEYCODE'
and CODE_KEY3 <> '*'
and CODE_KEY1 = 'B1'
and CODE_KEY2 = 'PI000') m
left join (select NVL(c.IRECT_LINE_MAN, 0.0) as line,
b.jig_code as jig_code,
a.DIV_CODE as div_code,
NVL(b.NET_JPH, 0.0) as netjph,
NVL(b.ACTTACKTIME, 0.0) as att
from PRO_T_I08 a,
(select * from PRO_T_Q03 where head = 'B1' and div_code = 'PI000') b,
(select sum(DIRECT_LINE_MAN) as IRECT_LINE_MAN, jig_code
from PRO_T_I08 where div_code = 'PI000'
group by jig_code) c
where a.div_code = 'PI000'
and b.jig_code = a.jig_code(+)
and c.jig_code = a.jig_code) n on m.jig_code = n.jig_code出现问题的地方是这个地方
b:
select NVL(c.IRECT_LINE_MAN, 0.0) as line,
b.jig_code as jig_code,
a.DIV_CODE as div_code,
NVL(b.NET_JPH, 0.0) as netjph,
NVL(b.ACTTACKTIME, 0.0) as att
from PRO_T_I08 a,
(select * from PRO_T_Q03 where head = 'B1' and div_code = 'PI000') b,
(select sum(DIRECT_LINE_MAN) as IRECT_LINE_MAN, jig_code
from PRO_T_I08 where div_code = 'PI000'
group by jig_code) c
where a.div_code = 'PI000'
and b.jig_code = a.jig_code(+)
and c.jig_code = a.jig_code
这个查询语句查不出数据,
其实这个查询出来数据了。
C:
select * from PRO_T_Q03 where head = 'B1' and div_code = 'PI000') b
现在想的效果是如果b的sql语句查询不出数据。那么left join 后面是c的sql语句去执行。
a:
select m.jig_code, NVL(n.line, 0.0) as line, n.div_code, NVL(n.netjph, 0.0) as netjph, NVL(n.att, 0.0) as att
from (select CODE_KEY3 as jig_code
from pro_t_a05
where CODE_GRP = 'CONVEYCODE'
and CODE_KEY3 <> '*'
and CODE_KEY1 = 'B1'
and CODE_KEY2 = 'PI000') m
left join (select NVL(c.IRECT_LINE_MAN, 0.0) as line,
b.jig_code as jig_code,
a.DIV_CODE as div_code,
NVL(b.NET_JPH, 0.0) as netjph,
NVL(b.ACTTACKTIME, 0.0) as att
from PRO_T_I08 a,
(select * from PRO_T_Q03 where head = 'B1' and div_code = 'PI000') b,
(select sum(DIRECT_LINE_MAN) as IRECT_LINE_MAN, jig_code
from PRO_T_I08 where div_code = 'PI000'
group by jig_code) c
where a.div_code = 'PI000'
and b.jig_code = a.jig_code(+)
and c.jig_code = a.jig_code) n on m.jig_code = n.jig_code出现问题的地方是这个地方
b:
select NVL(c.IRECT_LINE_MAN, 0.0) as line,
b.jig_code as jig_code,
a.DIV_CODE as div_code,
NVL(b.NET_JPH, 0.0) as netjph,
NVL(b.ACTTACKTIME, 0.0) as att
from PRO_T_I08 a,
(select * from PRO_T_Q03 where head = 'B1' and div_code = 'PI000') b,
(select sum(DIRECT_LINE_MAN) as IRECT_LINE_MAN, jig_code
from PRO_T_I08 where div_code = 'PI000'
group by jig_code) c
where a.div_code = 'PI000'
and b.jig_code = a.jig_code(+)
and c.jig_code = a.jig_code
这个查询语句查不出数据,
其实这个查询出来数据了。
C:
select * from PRO_T_Q03 where head = 'B1' and div_code = 'PI000') b
现在想的效果是如果b的sql语句查询不出数据。那么left join 后面是c的sql语句去执行。
select m.jig_code,
NVL(n.line, 0.0) as line,
n.div_code,
NVL(n.netjph, 0.0) as netjph,
NVL(n.att, 0.0) as att
from (select CODE_KEY3 as jig_code
from pro_t_a05
where CODE_GRP = 'CONVEYCODE'
and CODE_KEY3 <> '*'
and CODE_KEY1 = 'B1'
and CODE_KEY2 = 'PI000') m
left join(case when (select count(*)
from PRO_T_I08 a,
(select *
from PRO_T_Q03
where head = 'B1'
and div_code = 'PI000') b,
(select sum(DIRECT_LINE_MAN) as IRECT_LINE_MAN,
jig_code
from PRO_T_I08
where div_code = 'PI000'
group by jig_code) c
where a.div_code = 'PI000'
and b.jig_code = a.jig_code(+)
and c.jig_code = a.jig_code) = 0 then (select *
from PRO_T_Q03
where head = 'B1'
and div_code =
'PI000') n else (select NVL(c.IRECT_LINE_MAN,
0.0) as line,
b.jig_code as jig_code,
a.DIV_CODE as div_code,
NVL(b.NET_JPH,
0.0) as netjph,
NVL(b.ACTTACKTIME,
0.0) as att
from PRO_T_I08 a,
(select *
from PRO_T_Q03
where head = 'B1'
and div_code =
'PI000') b,
(select sum(DIRECT_LINE_MAN) as IRECT_LINE_MAN,
jig_code
from PRO_T_I08
where div_code =
'PI000'
group by jig_code) c
where a.div_code =
'PI000'
and b.jig_code =
a.jig_code(+)
and c.jig_code =
a.jig_code) non m.jig_code = n.jig_code
/--试一下: select m.jig_code,
NVL(m.line, 0.0) as line,
m.div_code,
NVL(m.netjph, 0.0) as netjph,
NVL(m.att, 0.0) as att
from (select CODE_KEY3 as jig_code
from pro_t_a05
where CODE_GRP = 'CONVEYCODE'
and CODE_KEY3 <> '*'
and CODE_KEY1 = 'B1'
and CODE_KEY2 = 'PI000') m
left join((select k.jig_code,
NVL(k.line, 0.0) as line,
k.div_code,
NVL(k.netjph, 0.0) as netjph,
NVL(k.att, 0.0) as att
from PRO_T_Q03 k
where head = 'B1'
and div_code = 'PI000')
union (select NVL(c.IRECT_LINE_MAN, 0.0) as line,
b.jig_code as jig_code,
a.DIV_CODE as div_code,
NVL(b.NET_JPH, 0.0) as netjph,
NVL(b.ACTTACKTIME, 0.0) as att
from PRO_T_I08 a,
(select *
from PRO_T_Q03
where head = 'B1'
and div_code = 'PI000') b,
(select sum(DIRECT_LINE_MAN) as IRECT_LINE_MAN, jig_code
from PRO_T_I08
where div_code = 'PI000'
group by jig_code) c
where a.div_code = 'PI000'
and b.jig_code = a.jig_code(+)
and c.jig_code = a.jig_code));
自已调试一下吧,估计还有括号问题,不好意思 :-(
ls的看起来case when 也可以。
NVL(n.line, 0.0) as line,
n.div_code,
NVL(n.netjph, 0.0) as netjph,
NVL(n.att, 0.0) as att
如果用那语句c就没有那些字段了就不一样了。楼主写的那语句是我上次写的,是简单的拼接,我让楼主仔细分析测试再修改这条语句。别人不好给答案的,除非你把所有的表数据都给出来。
楼主还是一点点测试再写吧。或者不要用我的那语句,那语句就是简单的拼接,效率极低.
但是就是不知道效率怎么样。