如果left join的表为空，就去执行这条sql语句

Question

这是整个的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语句去执行。

Answer 1

就是说如果b的sql语句什么都查询不出来。就用c的sql语句去代替。

Answer 2

--试试，用case when
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

Answer 3

/--试一下：  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));

Answer 4

少了end,真郁闷
自已调试一下吧，估计还有括号问题，不好意思　:-(

Answer 5

可以用decode()函数么？你就判断B表的一个非空字段是否为null，为空就取C表，把把这个当个数据集。。
ls的看起来case when 也可以。

Answer 6

楼上的这简单的拼接不行的。因为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
如果用那语句c就没有那些字段了就不一样了。楼主写的那语句是我上次写的，是简单的拼接，我让楼主仔细分析测试再修改这条语句。别人不好给答案的，除非你把所有的表数据都给出来。
楼主还是一点点测试再写吧。或者不要用我的那语句，那语句就是简单的拼接，效率极低.

Answer 7

不明白，我也做过啊，用的就是decode函数，可以完成的。
但是就是不知道效率怎么样。

Answer 8

最好明白需求，要不然这样盲目改SQL效率也低，而且也不知道是否正确。