ORA-01416:两表无法彼此外部连接

[yanshuqiang123]

报错sql如下：
select ZWTHYS_ID,ZWTHYS_THID,ZWTHYS_DWBH,ZWTHYS_THKM,ZWKMZD_KMBH ZWTHYS_THKM_ZWKMZD_KMBH,ZWKMZD_KMQC ZWTHYS_THKM_ZWKMZD_KMMC,ZWTHYS_BMBH,LSBMZD_BMQC ZWTHYS_BMBH_LSBMZD_BMMC,ZWTHYS_WLDWBH,LSWLDW_DWMC ZWTHYS_WLDWBH_LSWLDW_DWMC,ZWTHYS_CPBH,LSCP_CPMC ZWTHYS_CPBH_LSCP_CPMC,ZWTHYS_ZGBH,LSZGZD_ZGXM ZWTHYS_ZGBH_LSZGZD_ZGXM,ZWTHYS_WBBH,LSWBZD_BZMC ZWTHYS_WBBH_LSWBZD_BZMC,ZWTHYS_XMBH,ISNULL(LSHSXM0.LSHSXM_XMQC,'')||','||ISNULL(LSHSXM1.LSHSXM_XMQC,'')||','||ISNULL(LSHSXM2.LSHSXM_XMQC,'')||','||ISNULL(LSHSXM3.LSHSXM_XMQC,'')||','||ISNULL(LSHSXM4.LSHSXM_XMQC,'')||','||ISNULL(LSHSXM5.LSHSXM_XMQC,'')||','||ISNULL(LSHSXM6.LSHSXM_XMQC,'')||','||ISNULL(LSHSXM7.LSHSXM_XMQC,'')||','||ISNULL(LSHSXM8.LSHSXM_XMQC,'')||','||ISNULL(LSHSXM9.LSHSXM_XMQC,'')||','||ISNULL(LSHSXM10.LSHSXM_XMQC,'') ZWTHYS_XMBH_LSHSXM_XMQC,isNull(ZWTHYS_WB,0) ZWTHYS_WB,isNull(ZWTHYS_JE,0) ZWTHYS_JE,isNull(ZWTHYS_HL,0) ZWTHYS_HL,isNull(ZWTHYS_CE,0) ZWTHYS_CE,round((case isNull(ZWTHYS_HL,0) when '0' then 0 else isNull(ZWTHYS_CE,0)+isNull(ZWTHYS_JE,0) end), 2) ZWTHYS_TZJE
from ZWTHYS2015
left join ZWKMZD2015 on ZWTHYS_THKM=ZWKMZD_ID
left join LSWBZD on ZWTHYS_WBBH=LSWBZD_BZBH and (LSWBZD_TYBZ='0' or(LSWBZD_TYBZ='1' and LSWBZD_TYND>='2015'))
left join LSBMZD on LSBMZD_BMBH = ZWTHYS_BMBH and LSBMZD_DWBH = ZWTHYS_DWBH
left join LSWLDW on LSWLDW_WLDWBH = ZWTHYS_WLDWBH
left join LSCP2015 on LSCP_CPBH = ZWTHYS_CPBH and (LSCP_DWBH = ' ' or LSCP_DWBH = ZWTHYS_DWBH)
left join LSZGZD on ZWTHYS_DWBH = LSZGZD_DWBH and LSZGZD_ZGBH = ZWTHYS_ZGBH
left join LSHSXM2015 LSHSXM0 on LSHSXM0.LSHSXM_XMBH = substring(ZWTHYS_XMBH,(6+1)*0 + 1,6) and LSHSXM0.LSHSXM_LBBH = '01' and (LSHSXM0.LSHSXM_DWBH = ZWTHYS_DWBH or LSHSXM0.LSHSXM_DWBH = ' ')
left join LSHSXM2015 LSHSXM1 on LSHSXM1.LSHSXM_XMBH = substring(ZWTHYS_XMBH,(6+1)*1 + 1,6) and LSHSXM1.LSHSXM_LBBH = '02' and (LSHSXM1.LSHSXM_DWBH = ZWTHYS_DWBH or LSHSXM1.LSHSXM_DWBH = ' ')
left join LSHSXM2015 LSHSXM2 on LSHSXM2.LSHSXM_XMBH = substring(ZWTHYS_XMBH,(6+1)*2 + 1,6) and LSHSXM2.LSHSXM_LBBH = '03' and (LSHSXM2.LSHSXM_DWBH = ZWTHYS_DWBH or LSHSXM2.LSHSXM_DWBH = ' ')
left join LSHSXM2015 LSHSXM3 on LSHSXM3.LSHSXM_XMBH = substring(ZWTHYS_XMBH,(6+1)*3 + 1,6) and LSHSXM3.LSHSXM_LBBH = '04' and (LSHSXM3.LSHSXM_DWBH = ZWTHYS_DWBH or LSHSXM3.LSHSXM_DWBH = ' ')
left join LSHSXM2015 LSHSXM4 on LSHSXM4.LSHSXM_XMBH = substring(ZWTHYS_XMBH,(6+1)*4 + 1,6) and LSHSXM4.LSHSXM_LBBH = '05' and (LSHSXM4.LSHSXM_DWBH = ZWTHYS_DWBH or LSHSXM4.LSHSXM_DWBH = ' ')
left join LSHSXM2015 LSHSXM5 on LSHSXM5.LSHSXM_XMBH = substring(ZWTHYS_XMBH,(6+1)*5 + 1,6) and LSHSXM5.LSHSXM_LBBH = '06' and (LSHSXM5.LSHSXM_DWBH = ZWTHYS_DWBH or LSHSXM5.LSHSXM_DWBH = ' ')
left join LSHSXM2015 LSHSXM6 on LSHSXM6.LSHSXM_XMBH = substring(ZWTHYS_XMBH,(6+1)*6 + 1,6) and LSHSXM6.LSHSXM_LBBH = '07' and (LSHSXM6.LSHSXM_DWBH = ZWTHYS_DWBH or LSHSXM6.LSHSXM_DWBH = ' ')
left join LSHSXM2015 LSHSXM7 on LSHSXM7.LSHSXM_XMBH = substring(ZWTHYS_XMBH,(6+1)*7 + 1,6) and LSHSXM7.LSHSXM_LBBH = '08' and (LSHSXM7.LSHSXM_DWBH = ZWTHYS_DWBH or LSHSXM7.LSHSXM_DWBH = ' ')
left join LSHSXM2015 LSHSXM8 on LSHSXM8.LSHSXM_XMBH = substring(ZWTHYS_XMBH,(6+1)*8 + 1,6) and LSHSXM8.LSHSXM_LBBH = '09' and (LSHSXM8.LSHSXM_DWBH = ZWTHYS_DWBH or LSHSXM8.LSHSXM_DWBH = ' ')
left join LSHSXM2015 LSHSXM9 on LSHSXM9.LSHSXM_XMBH = substring(ZWTHYS_XMBH,(6+1)*9 + 1,6) and LSHSXM9.LSHSXM_LBBH = '10' and (LSHSXM9.LSHSXM_DWBH = ZWTHYS_DWBH or LSHSXM9.LSHSXM_DWBH = ' ')
left join LSHSXM2015 LSHSXM10 on LSHSXM10.LSHSXM_XMBH = substring(ZWTHYS_XMBH,(6+1)*10 + 1,6) and LSHSXM10.LSHSXM_LBBH = '98' and (LSHSXM10.LSHSXM_DWBH = ZWTHYS_DWBH or LSHSXM10.LSHSXM_DWBH = ' ')
where ZWTHYS_THID='38fa9f9b-cf2a-47ce-a305-d81ee01abe10'
order by ZWKMZD_KMBH,ZWTHYS_WBBH

[卖水果的net]

1. isnull 和 substring 是SQL Server 中的语法，请确认，你在 ORACLE 中，是否有了同名函数。 2. 多个表联合查询时，建议 select 列和 order by 列表，都使用 表名.列名的方式，而不是只写列名。 3. 最关键的，你把错误信息贴出来，这个不好猜的。 PS：这个是地方ZW 的数据库吗？

[IceIsabel]

left join LSWBZD on ZWTHYS_WBBH=LSWBZD_BZBH and (LSWBZD_TYBZ='0' or(LSWBZD_TYBZ='1' and LSWBZD_TYND>='2015')) left join LSBMZD on LSBMZD_BMBH = ZWTHYS_BMBH and LSBMZD_DWBH = ZWTHYS_DWBH LSBMZD 这个表左连接两次，需要加别名

[alicejohn]

报什么错啊，这么干找好有难度

[lyangsoft]

SELECT ZWTHYS_ID,
       ZWTHYS_THID,
       ZWTHYS_DWBH,
       ZWTHYS_THKM,
       ZWKMZD_KMBH ZWTHYS_THKM_ZWKMZD_KMBH,
       ZWKMZD_KMQC ZWTHYS_THKM_ZWKMZD_KMMC,
       ZWTHYS_BMBH,
       LSBMZD_BMQC ZWTHYS_BMBH_LSBMZD_BMMC,
       ZWTHYS_WLDWBH,
       LSWLDW_DWMC ZWTHYS_WLDWBH_LSWLDW_DWMC,
       ZWTHYS_CPBH,
       LSCP_CPMC ZWTHYS_CPBH_LSCP_CPMC,
       ZWTHYS_ZGBH,
       LSZGZD_ZGXM ZWTHYS_ZGBH_LSZGZD_ZGXM,
       ZWTHYS_WBBH,
       LSWBZD_BZMC ZWTHYS_WBBH_LSWBZD_BZMC,
       ZWTHYS_XMBH,
       ISNULL(LSHSXM0.LSHSXM_XMQC, '') || ',' ||
       ISNULL(LSHSXM1.LSHSXM_XMQC, '') || ',' ||
       ISNULL(LSHSXM2.LSHSXM_XMQC, '') || ',' ||
       ISNULL(LSHSXM3.LSHSXM_XMQC, '') || ',' ||
       ISNULL(LSHSXM4.LSHSXM_XMQC, '') || ',' ||
       ISNULL(LSHSXM5.LSHSXM_XMQC, '') || ',' ||
       ISNULL(LSHSXM6.LSHSXM_XMQC, '') || ',' ||
       ISNULL(LSHSXM7.LSHSXM_XMQC, '') || ',' ||
       ISNULL(LSHSXM8.LSHSXM_XMQC, '') || ',' ||
       ISNULL(LSHSXM9.LSHSXM_XMQC, '') || ',' ||
       ISNULL(LSHSXM10.LSHSXM_XMQC, '') ZWTHYS_XMBH_LSHSXM_XMQC,
       ISNULL(ZWTHYS_WB, 0) ZWTHYS_WB,
       ISNULL(ZWTHYS_JE, 0) ZWTHYS_JE,
       ISNULL(ZWTHYS_HL, 0) ZWTHYS_HL,
       ISNULL(ZWTHYS_CE, 0) ZWTHYS_CE,
       ROUND((CASE ISNULL(ZWTHYS_HL, 0)
               WHEN '0' THEN
                0
               ELSE
                ISNULL(ZWTHYS_CE, 0) + ISNULL(ZWTHYS_JE, 0)
             END),
             2) ZWTHYS_TZJE
  FROM ZWTHYS2015
  LEFT JOIN ZWKMZD2015
    ON ZWTHYS_THKM = ZWKMZD_ID
  LEFT JOIN LSWBZD
    ON ZWTHYS_WBBH = LSWBZD_BZBH
   AND (LSWBZD_TYBZ = '0' OR (LSWBZD_TYBZ = '1' AND LSWBZD_TYND >= '2015'))
  LEFT JOIN LSBMZD
    ON LSBMZD_BMBH = ZWTHYS_BMBH
   AND LSBMZD_DWBH = ZWTHYS_DWBH
  LEFT JOIN LSWLDW
    ON LSWLDW_WLDWBH = ZWTHYS_WLDWBH
  LEFT JOIN LSCP2015
    ON LSCP_CPBH = ZWTHYS_CPBH
   AND (LSCP_DWBH = ' ' OR LSCP_DWBH = ZWTHYS_DWBH)
  LEFT JOIN LSZGZD
    ON ZWTHYS_DWBH = LSZGZD_DWBH
   AND LSZGZD_ZGBH = ZWTHYS_ZGBH
  LEFT JOIN LSHSXM2015 LSHSXM0
    ON LSHSXM0.LSHSXM_XMBH = SUBSTRING(ZWTHYS_XMBH, (6 + 1) * 0 + 1, 6)
   AND LSHSXM0.LSHSXM_LBBH = '01'
   AND (LSHSXM0.LSHSXM_DWBH = ZWTHYS_DWBH OR LSHSXM0.LSHSXM_DWBH = ' ')
  LEFT JOIN LSHSXM2015 LSHSXM1
    ON LSHSXM1.LSHSXM_XMBH = SUBSTRING(ZWTHYS_XMBH, (6 + 1) * 1 + 1, 6)
   AND LSHSXM1.LSHSXM_LBBH = '02'
   AND (LSHSXM1.LSHSXM_DWBH = ZWTHYS_DWBH OR LSHSXM1.LSHSXM_DWBH = ' ')
  LEFT JOIN LSHSXM2015 LSHSXM2
    ON LSHSXM2.LSHSXM_XMBH = SUBSTRING(ZWTHYS_XMBH, (6 + 1) * 2 + 1, 6)
   AND LSHSXM2.LSHSXM_LBBH = '03'
   AND (LSHSXM2.LSHSXM_DWBH = ZWTHYS_DWBH OR LSHSXM2.LSHSXM_DWBH = ' ')
  LEFT JOIN LSHSXM2015 LSHSXM3
    ON LSHSXM3.LSHSXM_XMBH = SUBSTRING(ZWTHYS_XMBH, (6 + 1) * 3 + 1, 6)
   AND LSHSXM3.LSHSXM_LBBH = '04'
   AND (LSHSXM3.LSHSXM_DWBH = ZWTHYS_DWBH OR LSHSXM3.LSHSXM_DWBH = ' ')
  LEFT JOIN LSHSXM2015 LSHSXM4
    ON LSHSXM4.LSHSXM_XMBH = SUBSTRING(ZWTHYS_XMBH, (6 + 1) * 4 + 1, 6)
   AND LSHSXM4.LSHSXM_LBBH = '05'
   AND (LSHSXM4.LSHSXM_DWBH = ZWTHYS_DWBH OR LSHSXM4.LSHSXM_DWBH = ' ')
  LEFT JOIN LSHSXM2015 LSHSXM5
    ON LSHSXM5.LSHSXM_XMBH = SUBSTRING(ZWTHYS_XMBH, (6 + 1) * 5 + 1, 6)
   AND LSHSXM5.LSHSXM_LBBH = '06'
   AND (LSHSXM5.LSHSXM_DWBH = ZWTHYS_DWBH OR LSHSXM5.LSHSXM_DWBH = ' ')
  LEFT JOIN LSHSXM2015 LSHSXM6
    ON LSHSXM6.LSHSXM_XMBH = SUBSTRING(ZWTHYS_XMBH, (6 + 1) * 6 + 1, 6)
   AND LSHSXM6.LSHSXM_LBBH = '07'
   AND (LSHSXM6.LSHSXM_DWBH = ZWTHYS_DWBH OR LSHSXM6.LSHSXM_DWBH = ' ')
  LEFT JOIN LSHSXM2015 LSHSXM7
    ON LSHSXM7.LSHSXM_XMBH = SUBSTRING(ZWTHYS_XMBH, (6 + 1) * 7 + 1, 6)
   AND LSHSXM7.LSHSXM_LBBH = '08'
   AND (LSHSXM7.LSHSXM_DWBH = ZWTHYS_DWBH OR LSHSXM7.LSHSXM_DWBH = ' ')
  LEFT JOIN LSHSXM2015 LSHSXM8
    ON LSHSXM8.LSHSXM_XMBH = SUBSTRING(ZWTHYS_XMBH, (6 + 1) * 8 + 1, 6)
   AND LSHSXM8.LSHSXM_LBBH = '09'
   AND (LSHSXM8.LSHSXM_DWBH = ZWTHYS_DWBH OR LSHSXM8.LSHSXM_DWBH = ' ')
  LEFT JOIN LSHSXM2015 LSHSXM9
    ON LSHSXM9.LSHSXM_XMBH = SUBSTRING(ZWTHYS_XMBH, (6 + 1) * 9 + 1, 6)
   AND LSHSXM9.LSHSXM_LBBH = '10'
   AND (LSHSXM9.LSHSXM_DWBH = ZWTHYS_DWBH OR LSHSXM9.LSHSXM_DWBH = ' ')
  LEFT JOIN LSHSXM2015 LSHSXM10
    ON LSHSXM10.LSHSXM_XMBH = SUBSTRING(ZWTHYS_XMBH, (6 + 1) * 10 + 1, 6)
   AND LSHSXM10.LSHSXM_LBBH = '98'
   AND (LSHSXM10.LSHSXM_DWBH = ZWTHYS_DWBH OR LSHSXM10.LSHSXM_DWBH = ' ')
 WHERE ZWTHYS_THID = '38fa9f9b-cf2a-47ce-a305-d81ee01abe10'
 ORDER BY ZWKMZD_KMBH, ZWTHYS_WBBH

只从sql里看不出错在哪里了，你把left都去掉试试

[yanshuqiang123]

经过仔细研究，应该是oracle数据库bug问题。 此贴已结