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_DWBHLSBMZD 这个表左连接两次,需要加别名
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都去掉试试
1. isnull 和 substring 是SQL Server 中的语法,请确认,你在 ORACLE 中,是否有了同名函数。 2. 多个表联合查询时,建议 select 列和 order by 列表,都使用 表名.列名的方式,而不是只写列名。 3. 最关键的,你把错误信息贴出来,这个不好猜的。PS:这个是地方ZW 的数据库吗?
left join LSBMZD on LSBMZD_BMBH = ZWTHYS_BMBH and LSBMZD_DWBH = ZWTHYS_DWBHLSBMZD 这个表左连接两次,需要加别名
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都去掉试试
2. 多个表联合查询时,建议 select 列和 order by 列表,都使用 表名.列名的方式,而不是只写列名。
3. 最关键的,你把错误信息贴出来,这个不好猜的。PS:这个是地方ZW 的数据库吗?