多表关联查询的子查询

2017-05-24 10:07:36 328 阅读 AI 写同款 · GEO 优化AI 选题数据监测一键分发 ›

MySQL多表关联查询+子查询_R MySQL多表关联查询+子查询 -- **************关联查询（多表查询）****************-- 多表查询规则：1）确定查询哪些表。 2）确定哪些字段。 3）表与表之间连接关系(规律：连接条件数量=表数量-1)。-- ***1.交叉连接查询（笛卡尔乘积，查询条数=表1数据条数*表2数据条数。基本不会用）=cross joinSELECT empName AS '姓名阅读详情



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表关联条件....但是这句一直报未正确结束....

...全文

sram设计流程+memory compiler整理 →。阅读详情

qq_36934345

基础和管理 17,379 成员 95,111 内容

+ 关注 + 加入社区

切换为时间正序

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');

完整脚本

多表关联查询的子查询

相关推荐