用full join 全连就行了
SELECT A.FPHM,A.SFXM,A.SFJE ,B.FPHM,B.SFXM,B.SFJE
FROM
(
SELECT A.FPHM,A.SFXM,A.SFJE FROM
(
SELECT MS_SFMX.FPHM FPHM,MS_SFMX.SFXM SFXM, SUM(ZJJE) SFJE FROM MS_MZXX,MS_SFMX
WHERE MS_MZXX.HZRQ >='2006.12.07' AND
MS_MZXX.HZRQ <='2006.12.08' and MS_MZXX.FPHM = MS_SFMX.FPHM AND not SFXM iN (2,3,4,21,22,23)
GROUP BY MS_SFMX.FPHM,MS_SFMX.SFXM ) A
full join
(
SELECT MS_SFMX.FPHM FPHM,MS_SFMX.SFXM SFXM,SUM(ZJJE) AS SFJE FROM MS_MZXX,MS_SFMX,MS_YJ01
WHERE MS_MZXX.HZRQ >='2006.12.07' AND
MS_MZXX.HZRQ <='2006.12.08' and MS_MZXX.FPHM = MS_SFMX.FPHM AND MS_MZXX.FPHM = MS_YJ01.FPHM AND SFXM NOT IN (2,3,4,21,22,23)
GROUP BY MS_SFMX.FPHM,MS_SFMX.SFXM ) B
)
on A.FPHM = B.FPHM AND A.SFXM = B.SFXM AND A.SFJE <> B.SFJE
SELECT A.FPHM,A.SFXM,A.SFJE ,B.FPHM,B.SFXM,B.SFJE
FROM
(
SELECT A.FPHM,A.SFXM,A.SFJE FROM
(
SELECT MS_SFMX.FPHM FPHM,MS_SFMX.SFXM SFXM, SUM(ZJJE) SFJE FROM MS_MZXX,MS_SFMX
WHERE MS_MZXX.HZRQ >='2006.12.07' AND
MS_MZXX.HZRQ <='2006.12.08' and MS_MZXX.FPHM = MS_SFMX.FPHM AND not SFXM iN (2,3,4,21,22,23)
GROUP BY MS_SFMX.FPHM,MS_SFMX.SFXM ) A
full join
(
SELECT MS_SFMX.FPHM FPHM,MS_SFMX.SFXM SFXM,SUM(ZJJE) AS SFJE FROM MS_MZXX,MS_SFMX,MS_YJ01
WHERE MS_MZXX.HZRQ >='2006.12.07' AND
MS_MZXX.HZRQ <='2006.12.08' and MS_MZXX.FPHM = MS_SFMX.FPHM AND MS_MZXX.FPHM = MS_YJ01.FPHM AND SFXM NOT IN (2,3,4,21,22,23)
GROUP BY MS_SFMX.FPHM,MS_SFMX.SFXM ) B
)
on A.FPHM = B.FPHM AND A.SFXM = B.SFXM AND A.SFJE <> B.SFJE
FROM
(
SELECT A.FPHM,A.SFXM,A.SFJE FROM
(
SELECT MS_SFMX.FPHM FPHM,MS_SFMX.SFXM SFXM, SUM(ZJJE) SFJE FROM MS_MZXX,MS_SFMX
WHERE MS_MZXX.HZRQ >='2006.12.07' AND
MS_MZXX.HZRQ <='2006.12.08' and MS_MZXX.FPHM = MS_SFMX.FPHM AND not SFXM iN (2,3,4,21,22,23)
GROUP BY MS_SFMX.FPHM,MS_SFMX.SFXM ) A
full join
(
SELECT MS_SFMX.FPHM FPHM,MS_SFMX.SFXM SFXM,SUM(ZJJE) AS SFJE FROM MS_MZXX,MS_SFMX,MS_YJ01
WHERE MS_MZXX.HZRQ >='2006.12.07' AND
MS_MZXX.HZRQ <='2006.12.08' and MS_MZXX.FPHM = MS_SFMX.FPHM AND MS_MZXX.FPHM = MS_YJ01.FPHM AND SFXM NOT IN (2,3,4,21,22,23)
GROUP BY MS_SFMX.FPHM,MS_SFMX.SFXM ) B
)
on A.FPHM = B.FPHM AND A.SFXM = B.SFXM
where nullif(A.SFJE,B.SFJE )is not null--加上这一段就OK了
(SELECT ms_sfmx.fphm ,ms_sfmx.sfxm , SUM(zjje) sfje
FROM ms_mzxx,ms_sfmx
WHERE ms_mzxx.hzrq >='2006.12.07'
AND ms_mzxx.hzrq <='2006.12.08'
AND ms_mzxx.fphm = ms_sfmx.fphm
AND sfxm NOT IN (2,3,4,21,22,23)
GROUP BY ms_sfmx.fphm,ms_sfmx.sfxm ) a
INNER JOIN
(SELECT ms_sfmx.fphm ,ms_sfmx.sfxm ,SUM(zjje) AS sfje
FROM ms_mzxx,ms_sfmx,ms_yj01
WHERE ms_mzxx.hzrq >='2006.12.07'
AND ms_mzxx.hzrq <='2006.12.08'
AND ms_mzxx.fphm = ms_sfmx.fphm
AND ms_mzxx.fphm = ms_yj01.fphm
AND sfxm NOT IN (2,3,4,21,22,23)
GROUP BY ms_sfmx.fphm,ms_sfmx.sfxm ) b
ON a.fphm = b.fphm
AND a.sfxm = b.sfxm
AND a.sfje <> b.sfje