多表关联查询的子查询

qq_36934345 2017-05-24 10:07:36

select a.1,b...... from biao a,biao b,biao c
where nvl(b.sfzh,b.jbxx_zjhm)=c.jd_zjhm(+) and nvl(b.sfzh,b.jbxx_zjhm)=(select e.zjhm from biao d,biao e where d.cb=e.cb)(+)
and ......

这种多个关联查询的子查询咋样弄。。。就是说我要根据d,e表中的cb关联出的zjhm再去作为跟b,c表关联条件....但是这句一直报未正确结束....
...全文
262 5 打赏 收藏 转发到动态 举报
AI 作业
写回复
用AI写文章
5 条回复
切换为时间正序
请发表友善的回复…
发表回复
qq_36934345 2017-05-25
  • 打赏
  • 举报
回复
引用 4 楼 qq_36934345 的回复:
[quote=引用 2 楼 jdsnhan 的回复:] 感觉太乱了就把条件摘出来,用with,如

with t as
 (select e.zjhm
    from yw_jfgl_cxjylsb_ls d, yw_jfgl_zhdzb e
   where d.ZHKHYBT = e.NBZH)
select a.jgh,
       a.jydm,
       a.jymc,
       a.cq,
       a.khrq,
       a.jyrq,
       a.dqrq,
       (case
         when jydm in ('070603',
                       '070604',
                       '070404',
                       '070203',
                       '070204',
                       '070503',
                       '070504',
                       '070613') then
          -jyje
         else
          jyje
       end) as jy,
       nvl(b.jbxx_gsjgh, b.gsjgh) as gsjgh,
       b.bzjgmc,
       nvl(b.sfzh, b.jbxx_zjhm) as sfzh,
       b.jbxx_sskhq,
       b.khxm,
       b.jbxx_xb,
       b.jbxx_nl,
       b.jbxx_vipklx,
       c.WH_KHJLXM,
       c.JD_QH_SHENG,
       c.JD_QH_SHI,
       c.JD_QH_XIANQU,
       c.JD_QH_ZHEN,
       c.jd_dz
  from lsb_ls a, j_and_hzb b, xxt_jdxxb c
 where nvl(b.sfzh, b.jbxx_zjhm) = c.jd_zjhm(+)
   and nvl(b.sfzh, b.jbxx_zjhm) = t.zjhm(+)
   and a.jymc is not null
   and a.jydm in ('070601',
                  '070602',
                  '070603',
                  '070604',
                  '070401',
                  '070404',
                  '070201',
                  '070202',
                  '070203',
                  '070204',
                  '070501',
                  '070503',
                  '070504',
                  '070612',
                  '070613')
   and (substr(a.jysj, 1, 10) >= '2017-05-01' and
        substr(a.jysj, 1, 10) <= '2017-05-20');

多谢,但是我现在想用e的数据对d表进行补全,因为d表中的sfzh有空值的情况,这样写不对,所以我想用e表对d表进行补全就可以了,不想多增加数据,这个怎么弄啊 [/quote] 关联条件也是d.ZHKHYBT = e.NBZH
qq_36934345 2017-05-25
  • 打赏
  • 举报
回复
引用 2 楼 jdsnhan 的回复:
感觉太乱了就把条件摘出来,用with,如

with t as
 (select e.zjhm
    from yw_jfgl_cxjylsb_ls d, yw_jfgl_zhdzb e
   where d.ZHKHYBT = e.NBZH)
select a.jgh,
       a.jydm,
       a.jymc,
       a.cq,
       a.khrq,
       a.jyrq,
       a.dqrq,
       (case
         when jydm in ('070603',
                       '070604',
                       '070404',
                       '070203',
                       '070204',
                       '070503',
                       '070504',
                       '070613') then
          -jyje
         else
          jyje
       end) as jy,
       nvl(b.jbxx_gsjgh, b.gsjgh) as gsjgh,
       b.bzjgmc,
       nvl(b.sfzh, b.jbxx_zjhm) as sfzh,
       b.jbxx_sskhq,
       b.khxm,
       b.jbxx_xb,
       b.jbxx_nl,
       b.jbxx_vipklx,
       c.WH_KHJLXM,
       c.JD_QH_SHENG,
       c.JD_QH_SHI,
       c.JD_QH_XIANQU,
       c.JD_QH_ZHEN,
       c.jd_dz
  from lsb_ls a, j_and_hzb b, xxt_jdxxb c
 where nvl(b.sfzh, b.jbxx_zjhm) = c.jd_zjhm(+)
   and nvl(b.sfzh, b.jbxx_zjhm) = t.zjhm(+)
   and a.jymc is not null
   and a.jydm in ('070601',
                  '070602',
                  '070603',
                  '070604',
                  '070401',
                  '070404',
                  '070201',
                  '070202',
                  '070203',
                  '070204',
                  '070501',
                  '070503',
                  '070504',
                  '070612',
                  '070613')
   and (substr(a.jysj, 1, 10) >= '2017-05-01' and
        substr(a.jysj, 1, 10) <= '2017-05-20');

多谢,但是我现在想用e的数据对d表进行补全,因为d表中的sfzh有空值的情况,这样写不对,所以我想用e表对d表进行补全就可以了,不想多增加数据,这个怎么弄啊
小当家e 2017-05-25
  • 打赏
  • 举报
回复
where (nvl(b.sfzh,b.jbxx_zjhm)=c.jd_zjhm(+) and 多了左括号,没右括号
jdsnhan 2017-05-25
  • 打赏
  • 举报
回复
感觉太乱了就把条件摘出来,用with,如

with t as
 (select e.zjhm
    from yw_jfgl_cxjylsb_ls d, yw_jfgl_zhdzb e
   where d.ZHKHYBT = e.NBZH)
select a.jgh,
       a.jydm,
       a.jymc,
       a.cq,
       a.khrq,
       a.jyrq,
       a.dqrq,
       (case
         when jydm in ('070603',
                       '070604',
                       '070404',
                       '070203',
                       '070204',
                       '070503',
                       '070504',
                       '070613') then
          -jyje
         else
          jyje
       end) as jy,
       nvl(b.jbxx_gsjgh, b.gsjgh) as gsjgh,
       b.bzjgmc,
       nvl(b.sfzh, b.jbxx_zjhm) as sfzh,
       b.jbxx_sskhq,
       b.khxm,
       b.jbxx_xb,
       b.jbxx_nl,
       b.jbxx_vipklx,
       c.WH_KHJLXM,
       c.JD_QH_SHENG,
       c.JD_QH_SHI,
       c.JD_QH_XIANQU,
       c.JD_QH_ZHEN,
       c.jd_dz
  from lsb_ls a, j_and_hzb b, xxt_jdxxb c
 where nvl(b.sfzh, b.jbxx_zjhm) = c.jd_zjhm(+)
   and nvl(b.sfzh, b.jbxx_zjhm) = t.zjhm(+)
   and a.jymc is not null
   and a.jydm in ('070601',
                  '070602',
                  '070603',
                  '070604',
                  '070401',
                  '070404',
                  '070201',
                  '070202',
                  '070203',
                  '070204',
                  '070501',
                  '070503',
                  '070504',
                  '070612',
                  '070613')
   and (substr(a.jysj, 1, 10) >= '2017-05-01' and
        substr(a.jysj, 1, 10) <= '2017-05-20');

qq_36934345 2017-05-24
  • 打赏
  • 举报
回复

select a.jgh,a.jydm,a.jymc,a.cq,a.khrq,a.jyrq,a.dqrq,(case when jydm in ('070603','070604','070404','070203','070204','070503','070504','070613')
           then -jyje
      else jyje end) as jy,nvl(b.jbxx_gsjgh,b.gsjgh) as gsjgh,b.bzjgmc,nvl(b.sfzh,b.jbxx_zjhm) as sfzh,b.jbxx_sskhq,b.khxm,    b.jbxx_xb,b.jbxx_nl,b.jbxx_vipklx,c.WH_KHJLXM,c.JD_QH_SHENG,c.JD_QH_SHI,c.JD_QH_XIANQU,c.JD_QH_ZHEN,c.jd_dz  from 
        lsb_ls a,
         j_and_hzb b,
          xxt_jdxxb c
  where (nvl(b.sfzh,b.jbxx_zjhm)=c.jd_zjhm(+) and 
nvl(b.sfzh,b.jbxx_zjhm)=(select e.zjhm from yw_jfgl_cxjylsb_ls d,yw_jfgl_zhdzb e where d.ZHKHYBT=e.NBZH)(+) 
      and a.jymc is not null 
          and a.jydm in ('070601','070602','070603','070604','070401','070404','070201','070202','070203','070204','070501','070503','070504','070612','070613')
         and (substr(a.jysj,1,10)>='2017-05-01' and substr(a.jysj,1,10)<='2017-05-20');

完整脚本

17,382

社区成员

发帖
与我相关
我的任务
社区描述
Oracle 基础和管理
社区管理员
  • 基础和管理社区
加入社区
  • 近7日
  • 近30日
  • 至今
社区公告
暂无公告

试试用AI创作助手写篇文章吧