17,377
社区成员
发帖
与我相关
我的任务
分享多表关联查询的子查询
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表关联条件....但是这句一直报未正确结束....
...全文
请发表友善的回复…
发表回复
qq_36934345 2017-05-25
- 打赏
- 举报
多谢，但是我现在想用e的数据对d表进行补全，因为d表中的sfzh有空值的情况，这样写不对，所以我想用e表对d表进行补全就可以了，不想多增加数据，这个怎么弄啊
[/quote]
关联条件也是d.ZHKHYBT = e.NBZH
qq_36934345 2017-05-25
- 打赏
- 举报
多谢，但是我现在想用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');
