台灣最大程式設計社群網站
線上人數
942
 
會員總數:239355
討論主題:185691
歡迎您免費加入會員
討論區列表 >> MS SQL >> SQL LEFT JOIN 問題
[ 變換順序 ]  
[我要回覆]
1
回應主題 加入我的關注話題 檢舉此篇討論 將提問者加入個人黑名單
SQL LEFT JOIN 問題
價值 : 50 QP  點閱數:161 回應數:1

樓主

孤鷹
門外漢
0 2
22 2
發送站內信



請問一下各位先進前輩
以下是我所有的語法
我應該把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


搜尋相關Tags的文章: [ SQL LEFT JOIN ] ,
本篇文章發表於2017-03-03 15:27
別忘捐VP感謝幫助你的人 新手會員瞧一瞧
1樓
回應

史努比
檢舉此回應
sql 2000:


left join:


使用left join 不應在from使用 , 逗號隔開 table

本篇文章回覆於2017-03-06 10:38
== 簽名檔 ==
--未登入的會員無法查看對方簽名檔--
[ 變換順序 ]   
1

回覆
如要回應,請先登入.