SQL LEFT JOIN 問題

sql left join

loveotgi 2017-03-03 15:04:06 ‧ 6389 瀏覽

分享至

請問一下各位先進前輩
以下是我所有的語法
我應該把SQL LEFT JOIN 寫在那裡
目前我是寫在最下方,但沒有任何資料出來
本來是sql 2000 用的是 *= 可以執行,也有資料
公司昇級成 sql 2016 就無法執行了

SELECT M.SALES+M.AREONO AS 複合鍵,N.MV002 AS 業務員,M.AREONO AS 區域, O.銷貨總金額,O.銷貨毛利, P.退貨總金額,P.退貨毛利, Q.寄庫總金額,Q.寄庫毛利, R.樣品成本, S.銷客數,T.退客數, U.新客戶 AS 開發新客戶
FROM (
SELECT AA.TG005 AS AREONO,AA.TG006 AS SALES
FROM COPTG AA, COPTH BB
WHERE (AA.TG001=BB.TH001 AND AA.TG002=BB.TH002) AND (AA.TG003 BETWEEN '20170101' AND '20170331') AND BB.TH020='Y' AND BB.TH001
IN ('2301','2302','2303','2304','2308','2309','2331','2332','2333','2334','2339') UNION
SELECT II.TI005 AS AREONO,II.TI006 AS SALES
FROM COPTI II, COPTJ JJ
WHERE (II.TI001=JJ.TJ001 AND II.TI002=JJ.TJ002) AND (II.TI003 BETWEEN '20170101' AND '20170331') AND JJ.TJ021='Y' AND JJ.TJ001
IN ('2401','2404','2408','2409','2431','2434','2439') )
M ,
CMSMV
N ,(
SELECT C.TG005 AS AREO_O,C.TG006 AS SALES, SUM(A.TH013) /1.05 AS 銷貨總金額, SUM(A.TH035-(B.MB057+B.MB058+B.MB059+B.MB060)*A.TH008-(B.MB057+B.MB058+B.MB059+B.MB060)A.TH024) AS 銷貨毛利
FROM COPTH A,INVMB B,COPTG C
WHERE (C.TG003 BETWEEN '20170101' AND '20170331') AND TH020='Y' AND A.TH001
IN ('2301','2302','2304','2308','2309','2331','2332','2334','2339') AND A.TH004=B.MB001 AND (A.TH001=C.TG001 AND A.TH002=C.TG002)
GROUP BY C.TG005,C.TG006 ) O ,(
SELECT F.TI005 AS AREO_F,F.TI006 AS SALES, SUM(E.TJ012)/1.05 AS 退貨總金額, SUM(E.TJ031-(B.MB057+B.MB058+B.MB059+B.MB060)(E.TJ007+E.TJ042)) AS 退貨毛利
FROM COPTJ
E ,INVMB B, COPTI F
WHERE (F.TI003 BETWEEN '20170101' AND '20170331') AND E.TJ021='Y' AND E.TJ001
IN ('2401','2404','2408','2409','2431','2434','2439') AND E.TJ004=B.MB001 AND (E.TJ001=F.TI001 AND E.TJ002=F.TI002)
GROUP BY F.TI005,F.TI006 )
P ,(
SELECT C.TG005 AS AREO_Q,C.TG006 AS SALES, SUM(A.TH013) AS 寄庫總金額, SUM(A.TH035-(B.MB057+B.MB058+B.MB059+B.MB060)*A.TH008-(B.MB057+B.MB058+B.MB059+B.MB060)*A.TH024) AS 寄庫毛利
FROM COPTH A,INVMB B,COPTG C
WHERE (C.TG003 BETWEEN '20170101' AND '20170331') AND TH020='Y' AND (TH001='2308') AND A.TH004=B.MB001 AND (A.TH001=C.TG001 AND A.TH002=C.TG002)
GROUP BY C.TG005,C.TG006 )Q ,(
SELECT C.TG005 AS AREO_R,C.TG006 AS SALES, SUM((B.MB057+B.MB058+B.MB059+B.MB060)*A.TH008+(B.MB057+B.MB058+B.MB059+B.MB060)*A.TH024) AS 樣品成本

FROM COPTH A , INVMB B, COPTG C
WHERE (C.TG003 BETWEEN '20170101' AND '20170331') AND TH020='Y' AND TH001
IN ('2303','2333') AND A.TH004=B.MB001 AND (A.TH001=C.TG001 AND A.TH002=C.TG002)
GROUP BY C.TG005,C.TG006 )R ,(
SELECT TG005 AS AREO_S,TG006 AS SALES,COUNT(DISTINCT TG004) AS 銷客數
FROM COPTG
WHERE (TG003 BETWEEN '20170101' AND '20170331') AND TG023='Y' AND TG001
IN ('2301','2302','2304','2308','2309','2331','2332','2334','2339')
GROUP BY TG005,TG006 )S ,
(
SELECT TI005 AS AREO_T,TI006 AS SALES,COUNT(DISTINCT TI004) AS 退客數
FROM COPTI
WHERE (TI003 BETWEEN '20170101' AND '20170331') AND TI019='Y' AND TI001
IN ('2401','2404','2408','2409','2431','2434','2439')
GROUP BY TI005,TI006 )T ,(
SELECT MA015 AS AREO_U,MA016 AS SALES,
COUNT(MA001) AS 新客戶
FROM COPMA
WHERE (CREATE_DATE BETWEEN '20170101' AND '20170331')
GROUP BY MA015,MA016 )U
SELECT M.* FROM M LEFT JOIN N ON M.SALES=N.MV001
LEFT JOIN O ON M.SALES=O.SALES
LEFT JOIN P ON M.SALES=P.SALES
LEFT JOIN Q ON M.SALES=Q.SALES
LEFT JOIN R ON M.SALES=R.SALES
LEFT JOIN S ON M.SALES=S.SALES
LEFT JOIN T ON M.SALES=T.SALES
LEFT JOIN U ON M.SALES=U.SALES
LEFT JOIN O ON M.AREONO=O.AREO_O
LEFT JOIN P ON M.AREONO=P.AREO_F
LEFT JOIN Q ON M.AREONO=Q.AREO_Q
LEFT JOIN R ON M.AREONO=R.AREO_R
LEFT JOIN S ON M.AREONO=S.AREO_S
LEFT JOIN T ON M.AREONO=T.AREO_T
LEFT JOIN U ON M.AREONO=U.AREO_U

ORDER BY M.AREONO

登入發表討論

直播研討會

{{ item.channelVendor }} {{ item.webinarstarted }} |

直播中

1 個回答

做工仔人!

iT邦大師 1 級 ‧ 2017-03-03 15:28:06

最佳解答

SELECT M.SALES+M.AREONO AS 複合鍵,N.MV002 AS 業務員,M.AREONO AS 區域, O.銷貨總金額,O.銷貨毛利, P.退貨總金額,P.退貨毛利, Q.寄庫總金額,Q.寄庫毛利, R.樣品成本, S.銷客數,T.退客數, U.新客戶 AS 開發新客戶
FROM (

SELECT AA.TG005 AS AREONO,AA.TG006 AS SALES
FROM COPTG AA, COPTH BB
WHERE (AA.TG001=BB.TH001 AND AA.TG002=BB.TH002) AND (AA.TG003 BETWEEN '20170101' AND '20170331') AND BB.TH020='Y' AND BB.TH001
IN ('2301','2302','2303','2304','2308','2309','2331','2332','2333','2334','2339')
UNION
SELECT II.TI005 AS AREONO,II.TI006 AS SALES
FROM COPTI II, COPTJ JJ
WHERE (II.TI001=JJ.TJ001 AND II.TI002=JJ.TJ002) AND (II.TI003 BETWEEN '20170101' AND '20170331') AND JJ.TJ021='Y' AND JJ.TJ001
IN ('2401','2404','2408','2409','2431','2434','2439')
)M LEFT JOIN CMSMV N ON M.SALES=N.MV001  

   LEFT JOIN (
SELECT C.TG005 AS AREO_O,C.TG006 AS SALES, SUM(A.TH013) /1.05 AS 銷貨總金額, SUM(A.TH035-(B.MB057+B.MB058+B.MB059+B.MB060)*A.TH008-(B.MB057+B.MB058+B.MB059+B.MB060)*A.TH024) AS 銷貨毛利
FROM COPTH A,INVMB B,COPTG C
WHERE (C.TG003 BETWEEN '20170101' AND '20170331') AND TH020='Y' AND A.TH001
IN ('2301','2302','2304','2308','2309','2331','2332','2334','2339') AND A.TH004=B.MB001 AND (A.TH001=C.TG001 AND A.TH002=C.TG002)
GROUP BY C.TG005,C.TG006 ) O ON M.SALES=O.SALES AND M.AREONO=O.AREO_O  

   LEFT JOIN (
SELECT F.TI005 AS AREO_F,F.TI006 AS SALES, SUM(E.TJ012)/1.05 AS 退貨總金額, SUM(E.TJ031-(B.MB057+B.MB058+B.MB059+B.MB060)(E.TJ007+E.TJ042)) AS 退貨毛利
FROM COPTJ
E ,INVMB B, COPTI F
WHERE (F.TI003 BETWEEN '20170101' AND '20170331') AND E.TJ021='Y' AND E.TJ001
IN ('2401','2404','2408','2409','2431','2434','2439') AND E.TJ004=B.MB001 AND (E.TJ001=F.TI001 AND E.TJ002=F.TI002)
GROUP BY F.TI005,F.TI006 ) P ON M.SALES=P.SALES AND M.AREONO=P.AREO_F  

LEFT JOIN (
SELECT C.TG005 AS AREO_Q,C.TG006 AS SALES, SUM(A.TH013) AS 寄庫總金額, SUM(A.TH035-(B.MB057+B.MB058+B.MB059+B.MB060)*A.TH008-(B.MB057+B.MB058+B.MB059+B.MB060)*A.TH024) AS 寄庫毛利
FROM COPTH A,INVMB B,COPTG C
WHERE (C.TG003 BETWEEN '20170101' AND '20170331') AND TH020='Y' AND (TH001='2308') AND A.TH004=B.MB001 AND (A.TH001=C.TG001 AND A.TH002=C.TG002)
GROUP BY C.TG005,C.TG006 )Q ON M.SALES=Q.SALES AND M.AREONO=Q.AREO_Q  

LEFT JOIN (
SELECT C.TG005 AS AREO_R,C.TG006 AS SALES, SUM((B.MB057+B.MB058+B.MB059+B.MB060)*A.TH008+(B.MB057+B.MB058+B.MB059+B.MB060)*A.TH024) AS 樣品成本
FROM COPTH A , INVMB B, COPTG C
WHERE (C.TG003 BETWEEN '20170101' AND '20170331') AND TH020='Y' AND TH001
IN ('2303','2333') AND A.TH004=B.MB001 AND (A.TH001=C.TG001 AND A.TH002=C.TG002)
GROUP BY C.TG005,C.TG006 )R ON M.SALES=R.SALES AND M.AREONO=R.AREO_R 

LEFT JOIN (
SELECT TG005 AS AREO_S,TG006 AS SALES,COUNT(DISTINCT TG004) AS 銷客數
FROM COPTG
WHERE (TG003 BETWEEN '20170101' AND '20170331') AND TG023='Y' AND TG001
IN ('2301','2302','2304','2308','2309','2331','2332','2334','2339')
GROUP BY TG005,TG006 )S ON M.SALES=S.SALES AND M.AREONO=S.AREO_S  

LEFT JOIN (
SELECT TI005 AS AREO_T,TI006 AS SALES,COUNT(DISTINCT TI004) AS 退客數
FROM COPTI
WHERE (TI003 BETWEEN '20170101' AND '20170331') AND TI019='Y' AND TI001
IN ('2401','2404','2408','2409','2431','2434','2439')
GROUP BY TI005,TI006 )T ON M.SALES=T.SALES AND M.AREONO=T.AREO_T  

LEFT JOIN (
SELECT MA015 AS AREO_U,MA016 AS SALES,
COUNT(MA001) AS 新客戶
FROM COPMA
WHERE (CREATE_DATE BETWEEN '20170101' AND '20170331')
GROUP BY MA015,MA016 )U ON M.SALES=U.SALES AND M.AREONO=U.AREO_U 

ORDER BY M.AREONO

第二種改法 :

SELECT M.SALES+M.AREONO AS 複合鍵,N.MV002 AS 業務員,M.AREONO AS 區域, O.銷貨總金額,O.銷貨毛利, P.退貨總金額,P.退貨毛利, Q.寄庫總金額,Q.寄庫毛利, R.樣品成本, S.銷客數,T.退客數, U.新客戶 AS 開發新客戶
FROM (
SELECT AA.TG005 AS AREONO,AA.TG006 AS SALES
FROM COPTG AA, COPTH BB
WHERE (AA.TG001=BB.TH001 AND AA.TG002=BB.TH002) AND (AA.TG003 BETWEEN '20170101' AND '20170331') AND BB.TH020='Y' AND BB.TH001
IN ('2301','2302','2303','2304','2308','2309','2331','2332','2333','2334','2339') UNION
SELECT II.TI005 AS AREONO,II.TI006 AS SALES
FROM COPTI II, COPTJ JJ
WHERE (II.TI001=JJ.TJ001 AND II.TI002=JJ.TJ002) AND (II.TI003 BETWEEN '20170101' AND '20170331') AND JJ.TJ021='Y' AND JJ.TJ001
IN ('2401','2404','2408','2409','2431','2434','2439') )
M ,
CMSMV
N ,(
SELECT C.TG005 AS AREO_O,C.TG006 AS SALES, SUM(A.TH013) /1.05 AS 銷貨總金額, SUM(A.TH035-(B.MB057+B.MB058+B.MB059+B.MB060)*A.TH008-(B.MB057+B.MB058+B.MB059+B.MB060)*A.TH024) AS 銷貨毛利
FROM COPTH A,INVMB B,COPTG C
WHERE (C.TG003 BETWEEN '20170101' AND '20170331') AND TH020='Y' AND A.TH001
IN ('2301','2302','2304','2308','2309','2331','2332','2334','2339') AND A.TH004=B.MB001 AND (A.TH001=C.TG001 AND A.TH002=C.TG002)
GROUP BY C.TG005,C.TG006 ) O ,(
SELECT F.TI005 AS AREO_F,F.TI006 AS SALES, SUM(E.TJ012)/1.05 AS 退貨總金額, SUM(E.TJ031-(B.MB057+B.MB058+B.MB059+B.MB060)(E.TJ007+E.TJ042)) AS 退貨毛利
FROM COPTJ
E ,INVMB B, COPTI F
WHERE (F.TI003 BETWEEN '20170101' AND '20170331') AND E.TJ021='Y' AND E.TJ001
IN ('2401','2404','2408','2409','2431','2434','2439') AND E.TJ004=B.MB001 AND (E.TJ001=F.TI001 AND E.TJ002=F.TI002)
GROUP BY F.TI005,F.TI006 )
P ,(
SELECT C.TG005 AS AREO_Q,C.TG006 AS SALES, SUM(A.TH013) AS 寄庫總金額, SUM(A.TH035-(B.MB057+B.MB058+B.MB059+B.MB060)*A.TH008-(B.MB057+B.MB058+B.MB059+B.MB060)*A.TH024) AS 寄庫毛利
FROM COPTH A,INVMB B,COPTG C
WHERE (C.TG003 BETWEEN '20170101' AND '20170331') AND TH020='Y' AND (TH001='2308') AND A.TH004=B.MB001 AND (A.TH001=C.TG001 AND A.TH002=C.TG002)
GROUP BY C.TG005,C.TG006 )Q ,(
SELECT C.TG005 AS AREO_R,C.TG006 AS SALES, SUM((B.MB057+B.MB058+B.MB059+B.MB060)*A.TH008+(B.MB057+B.MB058+B.MB059+B.MB060)*A.TH024) AS 樣品成本

FROM COPTH A , INVMB B, COPTG C
WHERE (C.TG003 BETWEEN '20170101' AND '20170331') AND TH020='Y' AND TH001
IN ('2303','2333') AND A.TH004=B.MB001 AND (A.TH001=C.TG001 AND A.TH002=C.TG002)
GROUP BY C.TG005,C.TG006 )R ,(
SELECT TG005 AS AREO_S,TG006 AS SALES,COUNT(DISTINCT TG004) AS 銷客數
FROM COPTG
WHERE (TG003 BETWEEN '20170101' AND '20170331') AND TG023='Y' AND TG001
IN ('2301','2302','2304','2308','2309','2331','2332','2334','2339')
GROUP BY TG005,TG006 )S ,
(
SELECT TI005 AS AREO_T,TI006 AS SALES,COUNT(DISTINCT TI004) AS 退客數
FROM COPTI
WHERE (TI003 BETWEEN '20170101' AND '20170331') AND TI019='Y' AND TI001
IN ('2401','2404','2408','2409','2431','2434','2439')
GROUP BY TI005,TI006 )T ,(
SELECT MA015 AS AREO_U,MA016 AS SALES,
COUNT(MA001) AS 新客戶
FROM COPMA
WHERE (CREATE_DATE BETWEEN '20170101' AND '20170331')
GROUP BY MA015,MA016 )U 

where M.SALES=O.SALES
AND M.SALES=P.SALES
AND M.SALES=Q.SALES
AND M.SALES=R.SALES
AND M.SALES=S.SALES
AND M.SALES=T.SALES
AND M.SALES=U.SALES
AND M.AREONO=O.AREO_O
AND M.AREONO=P.AREO_F
AND M.AREONO=Q.AREO_Q
AND M.AREONO=R.AREO_R
AND M.AREONO=S.AREO_S
AND M.AREONO=T.AREO_T
AND M.AREONO=U.AREO_U

ORDER BY M.AREONO

回應 4
分享
檢舉

看更多先前的回應...收起先前的回應...

loveotgi iT邦新手 4 級 ‧ 2017-03-03 16:43:01 檢舉

非常感激十二萬分謝意謝謝您

loveotgi iT邦新手 4 級 ‧ 2017-03-06 16:24:51 檢舉

前輩

SELECT E.MA015 AS 部門,C.TG042 AS 單據日期,C.TG005 AS 區域,D.MV002 AS 業務員,C.TG004 as 代號,E.MA002 AS 客戶, A.TH001 AS 單別, 
   
   A.TH002 AS 單號, A.TH004 AS 品號, A.TH005 AS 品名,A.TH008 AS 數量,TH024 AS 贈品量, 
   
   
  A.TH012 AS 單價, 
   A.TH013 AS 總金額 
 FROM COPTH A,INVMB B,COPTG C,CMSMV D,COPMA   E 
  
WHERE (C.TG003 BETWEEN '20160701'  AND '20160731')   AND TH020='Y' 
   AND  TH001 
   
  IN ('2301','2302','2304','2308','2309','2331','2332','2333','2334','2339') 
     AND A.TH004=B.MB001 AND (A.TH001=C.TG001 AND A.TH002=C.TG002) AND 
    C.TG006=D.MV001    AND C.TG004=E.MA001 
   
   UNION ALL 
   SELECT E.MA015 AS 部門,C.TI034 AS 單據日期,C.TI005 AS 區域,D.MV002 AS 業務員,C.TI004 AS 代號, E.MA002 AS 客戶, 
    A.TJ001 AS 單別, A.TJ002 AS 單號,A.TJ004 AS 品號,A.TJ005 AS 品名,  A.TJ007*-1  AS 數量,A.TJ042*-1 AS 贈品量, A.TJ011*-1 
  AS 單價,   A.TJ012*-1 AS 總金額 
   
  FROM COPTJ A ,INVMB B, COPTI C, CMSMV D, COPMA  E 
   WHERE (C.TI003 BETWEEN '20160701' AND  '20160731') AND TJ021='Y' 
      AND A.TJ001   IN ('2401','2404','2408','2409','2431','2434','2439') 
     AND A.TJ004=B.MB001 AND (A.TJ001=C.TI001 AND A.TJ002=C.TI002)   
     ```
     
LEFT JOIN CMSMV D ON  C.TI006=D.MV001 AND C.TI004=E.MA001 
   
  ORDER BY 區域,單別,單號
  我照您的做法在最下面加上 LEFT JOIN CMSMV D ON  C.TI006=D.MV001 AND C.TI004=E.MA001
  出現錯誤訊息 
  接近關鍵字 'LEFT'之處的語法不正確,請問是為什麼呢

做工仔人! iT邦大師 1 級 ‧ 2017-03-06 17:15:18 檢舉

沒有看到全部程式.我也不知您錯在那裡.
只能講一下觀念:
在 TABLE JOIN(不管那一種JOIN) 時,這個TABLE 必須要先被 USED.
如:
FROM COPTG A LEFT JOIN CMSMV B ON A.TG006=B.MV001 AND A.TG004=C.MA001
LEFT JOIN COPMA C ON A.TG004=C.MA001
=> 這樣寫法:會報錯.因為COPMA 是在 ON A.TG006=B.MV001 AND A.TG004=C.MA001 才被USED
如果改為:
FROM COPTG A LEFT JOIN COPMA C ON A.TG004=C.MA001
LEFT JOIN CMSMV B ON A.TG006=B.MV001 AND A.TG004=C.MA001
這樣就不會報錯. 因為COPMA 已經先被USED .

您若對JOIN 語法不熟, 可以改用下列語法:
FROM COPTG A , CMSMV B , COPMA C
WHERE A.TG006=B.MV001(+) AND A.TG004=C.MA001(+)
上述語法等同於:
FROM COPTG A LEFT JOIN COPMA C ON A.TG004=C.MA001
LEFT JOIN CMSMV B ON A.TG006=B.MV001