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

樓主

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



請問一下前輩及先進
WHERE M.CUS_NO*=O.CUS_NO AND M.SALES*=O.SALES
AND M.CUS_NO*=P.CUS_NO AND M.SALES*=P.SALES
AND M.CUS_NO*=Q.CUS_NO AND M.SALES*=Q.SALES
AND M.CUS_NO*=R.CUS_NO AND M.SALES*=R.SALES
AND M.CUS_NO*=S.CUS_NO AND M.SALES*=S.SALES
AND M.CUS_NO*=T.CUS_NO AND M.SALES*=T.SALES
AND M.CUS_NO*=U.CUS_NO AND M.SALES*=U.SALES
這是我在SQL 2000上的語法
可以執行
但最近公司 SQL昇級成2016了
在2016上執行出現
訊息 102,層級 15,狀態 1,程序 SP_61111,行 132 [批次開始行 7]
接近 '*=' 之處的語法不正確。
於是 我把 *拿掉了
結果 就沒有任何資料出來了
請前輩或先進救救我一下 啊不然 就要沒頭路了


搜尋相關Tags的文章: [ SQL 語法問題 ] ,
本篇文章發表於2017-03-01 16:50
別忘捐VP感謝幫助你的人 新手會員瞧一瞧
1樓
回應

pilipala
檢舉此回應
在 SQL 2000
*= 為 LEFT JOIN
=* 為 RIGHT JOIN
SQL 2005 開始就不支援這樣的寫法囉
本篇文章回覆於2017-03-01 21:58
== 簽名檔 ==
--未登入的會員無法查看對方簽名檔--
2樓
https://msdn.microsoft.com/zh-tw/library/bb510680.aspx
把sql 相容性等級調整一下 試試
本篇文章回覆於2017-03-02 05:11
== 簽名檔 ==
--未登入的會員無法查看對方簽名檔--
3樓
作者回應

孤鷹
檢舉此回應
前輩感激不盡
本篇文章回覆於2017-03-02 08:12
== 簽名檔 ==
--未登入的會員無法查看對方簽名檔--
4樓
回應

pilipala
檢舉此回應
SQL 2016 相容性層級,最低只能調到 SQL 2008 (100) 喔,
要能向下相容到 SQL 2000 的版本,只到 SQL 2008 R2 為止
本篇文章回覆於2017-03-02 08:25
== 簽名檔 ==
--未登入的會員無法查看對方簽名檔--
5樓
作者回應

孤鷹
檢舉此回應
前輩,因為前任的語法太過於複雜 我不知道應該把LEFT JOIN放置那裡,我把全部的語法貼上,您可以幫幫我嗎
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 '20170301' 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=N.MV001 AND M.AREONO=O.AREO_O AND M.SALES=O.SALES AND M.AREONO=P.AREO_F AND M.SALES=P.SALES AND M.AREONO=Q.AREO_Q AND M.SALES=Q.SALES AND M.AREONO=R.AREO_R AND M.SALES=R.SALES AND M.AREONO=S.AREO_S AND M.SALES=S.SALES AND M.AREONO=T.AREO_T AND M.SALES=T.SALES AND M.AREONO=U.AREO_U AND M.SALES=U.SALES
ORDER BY M.AREONO
本篇文章回覆於2017-03-03 12:03
== 簽名檔 ==
--未登入的會員無法查看對方簽名檔--
6樓
回應

風燭殘年
捐贈 VP 給 風燭殘年 檢舉此回應
應該就這樣吧...

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 '20170301' 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.AREONO=O.AREO_O AND M.SALES=O.SALES

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.AREONO=P.AREO_F AND M.SALES=P.SALES

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.AREONO=Q.AREO_Q AND M.SALES=Q.SALES

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.AREONO=R.AREO_R AND M.SALES=R.SALES

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.AREONO=S.AREO_S AND M.SALES=S.SALES

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.AREONO=T.AREO_T AND M.SALES=T.SALES

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.AREONO=U.AREO_U AND M.SALES=U.SALES

ORDER BY M.AREONO
本篇文章回覆於2017-03-06 00:01
== 簽名檔 ==
--未登入的會員無法查看對方簽名檔--
[ 變換順序 ]   
1

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