请教各位高手,以下为我写的一段SQL,是JOIN同一个表五次,但这样出来的结果是有好多重复的,请问JION同一个表,但想要根据别名来得到不同的结果还有什么方法吗?请指教,谢谢!急啊!
SELECT A.MA002 AS MA002, A.MA003 AS MA003, MB001,INV.MC002 AS LA009,
AA.LA011 AS AA_LA011,AA.LA013 AS AA_LA013,
BB.LA011 AS BB_LA011,BB.LA013 AS BB_LA013,CC.LA011 AS CC_LA011,CC.LA013 AS CC_LA013,
DD.LA011 AS DD_LA011,DD.LA013 DD_LA013,EE.LA011 AS EE_LA011,EE.LA013 AS EE_LA013,
A.MA004 AS MA004, B.MA003 AS ACTMA003,
MB002, MB003, MB004, MB072, MB090, CMS.MC002 AS CMSMC002
FROM INVMB AS INVMB
INNER JOIN INVMC AS INV ON INV.MC001=MB001
LEFT JOIN INVLA AS AA ON AA.LA001=MB001 and AA.LA006='1110' and AA.LA009=INV.MC002
LEFT JOIN INVLA AS BB ON BB.LA001=MB001 and (BB.LA006='2401' or BB.LA006='2402' or BB.LA006='240A')
LEFT JOIN INVLA AS CC ON CC.LA001=MB001 and (CC.LA006='1107' or CC.LA006='1113')
LEFT JOIN INVLA AS DD ON DD.LA001=MB001 and DD.LA006='1104' and DD.LA009=INV.MC002
LEFT JOIN INVLA AS EE ON EE.LA001=MB001 and (EE.LA006='1108' or EE.LA006='1109')
LEFT JOIN CMSMC AS CMS ON INV.MC002=CMS.MC001
LEFT JOIN INVMA AS A ON A.MA001='1' AND A.MA002=MB005
LEFT JOIN ACTMA AS B ON B.MA001=A.MA004
SELECT A.MA002 AS MA002, A.MA003 AS MA003, MB001,INV.MC002 AS LA009,
AA.LA011 AS AA_LA011,AA.LA013 AS AA_LA013,
BB.LA011 AS BB_LA011,BB.LA013 AS BB_LA013,CC.LA011 AS CC_LA011,CC.LA013 AS CC_LA013,
DD.LA011 AS DD_LA011,DD.LA013 DD_LA013,EE.LA011 AS EE_LA011,EE.LA013 AS EE_LA013,
A.MA004 AS MA004, B.MA003 AS ACTMA003,
MB002, MB003, MB004, MB072, MB090, CMS.MC002 AS CMSMC002
FROM INVMB AS INVMB
INNER JOIN INVMC AS INV ON INV.MC001=MB001
LEFT JOIN INVLA AS AA ON AA.LA001=MB001 and AA.LA006='1110' and AA.LA009=INV.MC002
LEFT JOIN INVLA AS BB ON BB.LA001=MB001 and (BB.LA006='2401' or BB.LA006='2402' or BB.LA006='240A')
LEFT JOIN INVLA AS CC ON CC.LA001=MB001 and (CC.LA006='1107' or CC.LA006='1113')
LEFT JOIN INVLA AS DD ON DD.LA001=MB001 and DD.LA006='1104' and DD.LA009=INV.MC002
LEFT JOIN INVLA AS EE ON EE.LA001=MB001 and (EE.LA006='1108' or EE.LA006='1109')
LEFT JOIN CMSMC AS CMS ON INV.MC002=CMS.MC001
LEFT JOIN INVMA AS A ON A.MA001='1' AND A.MA002=MB005
LEFT JOIN ACTMA AS B ON B.MA001=A.MA004
SELECT A.MA002 AS MA002, A.MA003 AS MA003, MB001,INV.MC002 AS LA009,
AA.LA011 AS AA_LA011,AA.LA013 AS AA_LA013,
BB.LA011 AS BB_LA011,BB.LA013 AS BB_LA013,CC.LA011 AS CC_LA011,CC.LA013 AS CC_LA013,
DD.LA011 AS DD_LA011,DD.LA013 DD_LA013,EE.LA011 AS EE_LA011,EE.LA013 AS EE_LA013,
A.MA004 AS MA004, B.MA003 AS ACTMA003,
MB002, MB003, MB004, MB072, MB090, CMS.MC002 AS CMSMC002
into #
FROM INVMB AS INVMB
INNER JOIN INVMC AS INV ON INV.MC001=MB001
LEFT JOIN INVLA AS AA ON AA.LA001=MB001 and AA.LA006='1110' and AA.LA009=INV.MC002
LEFT JOIN INVLA AS BB ON BB.LA001=MB001 and (BB.LA006='2401' or BB.LA006='2402' or BB.LA006='240A')
LEFT JOIN INVLA AS CC ON CC.LA001=MB001 and (CC.LA006='1107' or CC.LA006='1113')
LEFT JOIN INVLA AS DD ON DD.LA001=MB001 and DD.LA006='1104' and DD.LA009=INV.MC002
LEFT JOIN INVLA AS EE ON EE.LA001=MB001 and (EE.LA006='1108' or EE.LA006='1109')
LEFT JOIN CMSMC AS CMS ON INV.MC002=CMS.MC001
LEFT JOIN INVMA AS A ON A.MA001='1' AND A.MA002=MB005
LEFT JOIN ACTMA AS B ON B.MA001=A.MA004select * from # union select * from #