SELECT DISTINCT WRYJBXX.JGFZ || WRYJBXX.WRYBH,
/* tcty.zzyf,*/
XZ.JC AS JDB,
TZS.TZSBH,
TZS.WRYBH,
WRYJBXX.WRYMC,
TO_CHAR(TZS.JFQSRQ, 'YYYY-MM-DD') JFQSRQ,
TO_CHAR(TZS.JFZZRQ, 'YYYY-MM-DD') JFZZRQ,
TO_CHAR(TZS.FDRQ, 'YYYY-MM-DD') FDRQ,
TZS.YSJEZJ,
NVL(JMCL.JMJE, '0') AS JMJE,
TZS.YSZNJ,
(TZS.YSJEZJ - NVL(JMCL.JMJE, '0') + TZS.YSZNJ) AS YJJE,
(TZS.YSJEZJ - TZS.FQFY - TZS.ZSFY - TZS.GTFWFY -
TZS.WXFWFY) AS FSFY1,
(TZS.FSFY + TZS.FSJBFY) AS FSFY,
TZS.FQFY,
TZS.FSJBFY,
NVL(TZS.ZSFY, '0') AS ZSFY,
(TZS.GTFWFY + TZS.WXFWFY) AS GTFWFY,
TO_CHAR(FP.JRRQ, 'YYYY-MM-DD') JRRQ,
NVL(FP.JRJE, '0') AS JRJE,
NVL(TZS.QJJE, '0') AS QJJE,
GG.DMNR AS JFZT
FROM T_JCGL_PWSF_JFTZS TZS
LEFT JOIN T_JCGL_PWSF_JMCL JMCL ON JMCL.TZSBH = TZS.TZSBH
LEFT JOIN T_JCGL_PWSF_FP FP ON TZS.TZSBH = FP.TZSBH
LEFT JOIN T_WRY_JBXX WRYJBXX ON TZS.WRYBH = WRYJBXX.WRYBH
LEFT JOIN T_WRY_GLSX GLSX ON GLSX.WRYBH = WRYJBXX.WRYBH
LEFT JOIN T_WRY_SFYGL SFY ON WRYJBXX.WRYBH = SFY.WRYBH
LEFT JOIN T_ADMIN_RMS_YH YH ON YH.YHID = SFY.SFYBH
LEFT JOIN T_ADMIN_RMS_ZZJG ZZJG ON YH.BMBH = ZZJG.ZZBH
LEFT JOIN T_COMN_XZQHDM XZ ON XZ.XZQHDM = WRYJBXX.QFGWGDM
LEFT JOIN T_COMN_GGDMZ GG ON GG.DM = TZS.JFZT
AND TZS.ORGID = GG.ORGID
AND DMJBH = '166'
left join T_WRY_TCTY tcty on tcty.wrybh=wryjbxx.wrybh
WHERE 1 = 1 and tcty.zzyf is null这是我的一个查询语句,现在有一个问题,不加任何条件的时候,有68285条数据,加条件tcty.zzyf is null后,有65528条数据,条件改成tcty.zzyf is not null之后,却有3666,加了条件的数据之和比没加数据的要多,这是为什么???表里没有空字符串的数据...问题出在哪??跪求各位大侠帮帮忙!!谢谢啦
/* tcty.zzyf,*/
XZ.JC AS JDB,
TZS.TZSBH,
TZS.WRYBH,
WRYJBXX.WRYMC,
TO_CHAR(TZS.JFQSRQ, 'YYYY-MM-DD') JFQSRQ,
TO_CHAR(TZS.JFZZRQ, 'YYYY-MM-DD') JFZZRQ,
TO_CHAR(TZS.FDRQ, 'YYYY-MM-DD') FDRQ,
TZS.YSJEZJ,
NVL(JMCL.JMJE, '0') AS JMJE,
TZS.YSZNJ,
(TZS.YSJEZJ - NVL(JMCL.JMJE, '0') + TZS.YSZNJ) AS YJJE,
(TZS.YSJEZJ - TZS.FQFY - TZS.ZSFY - TZS.GTFWFY -
TZS.WXFWFY) AS FSFY1,
(TZS.FSFY + TZS.FSJBFY) AS FSFY,
TZS.FQFY,
TZS.FSJBFY,
NVL(TZS.ZSFY, '0') AS ZSFY,
(TZS.GTFWFY + TZS.WXFWFY) AS GTFWFY,
TO_CHAR(FP.JRRQ, 'YYYY-MM-DD') JRRQ,
NVL(FP.JRJE, '0') AS JRJE,
NVL(TZS.QJJE, '0') AS QJJE,
GG.DMNR AS JFZT
FROM T_JCGL_PWSF_JFTZS TZS
LEFT JOIN T_JCGL_PWSF_JMCL JMCL ON JMCL.TZSBH = TZS.TZSBH
LEFT JOIN T_JCGL_PWSF_FP FP ON TZS.TZSBH = FP.TZSBH
LEFT JOIN T_WRY_JBXX WRYJBXX ON TZS.WRYBH = WRYJBXX.WRYBH
LEFT JOIN T_WRY_GLSX GLSX ON GLSX.WRYBH = WRYJBXX.WRYBH
LEFT JOIN T_WRY_SFYGL SFY ON WRYJBXX.WRYBH = SFY.WRYBH
LEFT JOIN T_ADMIN_RMS_YH YH ON YH.YHID = SFY.SFYBH
LEFT JOIN T_ADMIN_RMS_ZZJG ZZJG ON YH.BMBH = ZZJG.ZZBH
LEFT JOIN T_COMN_XZQHDM XZ ON XZ.XZQHDM = WRYJBXX.QFGWGDM
LEFT JOIN T_COMN_GGDMZ GG ON GG.DM = TZS.JFZT
AND TZS.ORGID = GG.ORGID
AND DMJBH = '166'
left join T_WRY_TCTY tcty on tcty.wrybh=wryjbxx.wrybh
WHERE 1 = 1 and tcty.zzyf is null这是我的一个查询语句,现在有一个问题,不加任何条件的时候,有68285条数据,加条件tcty.zzyf is null后,有65528条数据,条件改成tcty.zzyf is not null之后,却有3666,加了条件的数据之和比没加数据的要多,这是为什么???表里没有空字符串的数据...问题出在哪??跪求各位大侠帮帮忙!!谢谢啦
因为进行那么多次左连接,每一次的中间表都要和下一个表进行左连接。
没用为null的数据,为什么要用左连接呢,做内连接就可以了。