iT邦幫忙

0

SQL 語法問題

ms sql
loveotgi 2017-03-01 16:39:21 ‧ 6019 瀏覽

  • 分享至 

  • xImage

請問一下前輩及先進

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]
接近 '*=' 之處的語法不正確。
於是 我把 *拿掉了
結果 就沒有任何資料出來了
請前輩或先進救救我一下 啊不然 就要沒頭路了

登入發表討論
圖片
  直播研討會
圖片
{{ item.channelVendor }} {{ item.webinarstarted }} |
{{ formatDate(item.duration) }}
直播中

3 個回答

0
king742171
iT邦新手 2 級 ‧ 2017-03-01 16:57:04

https://msdn.microsoft.com/zh-tw/library/ee240720(v=sql.120).aspx

參考看看吧

看更多先前的回應...收起先前的回應...
king742171 iT邦新手 2 級 ‧ 2017-03-01 16:57:21 檢舉

換個寫法

loveotgi iT邦新手 4 級 ‧ 2017-03-02 08:13:04 檢舉

前輩感激不盡

loveotgi iT邦新手 4 級 ‧ 2017-03-03 12:00:48 檢舉

前輩,因為我的語法太過複雜,我不知道應該吧 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
king742171 iT邦新手 2 級 ‧ 2017-03-03 15:53:01 檢舉

很抱歉...
太長了...
懶得看~= =
大概掃了一下你的SQL語法..
教學給的TABLE是兩個..
但你的TABLE卻不只兩個..
再給你一個參考網址
http://www.blueshop.com.tw/board/FUM20041006152735ZFS/BRD20091218101244JGI.html

另外..left outer join on (或是right)
放置位置是在from裡面
on..本身也有點where的意味在~
研究看看吧~
/images/emoticon/emoticon12.gif

登入發表回應
0
dscwferp
iT邦高手 1 級 ‧ 2017-03-04 11:57:49 
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 OUTER JOIN 
 CMSMV N  
 ON M.SALES=N.MV001 LEFT OUTER 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 OUTER 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 OUTER 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 OUTER 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 OUTER 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 OUTER 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 OUTER 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

請參考!

登入發表回應
0
davidliu9116
iT邦研究生 2 級 ‧ 2017-09-18 15:06:20 
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 inner join 
		       COPTH BB on AA.TG001=BB.TH001 AND AA.TG002=BB.TH002
         WHERE (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 inner join 
		       COPTJ JJ II.TI001=JJ.TJ001 AND II.TI002=JJ.TJ002
         WHERE (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 inner join
		       INVMB B on A.TH004=B.MB001 inner join
			   COPTG C on A.TH001=C.TG001 AND A.TH002=C.TG002
         WHERE (C.TG003 BETWEEN '20170301' AND '20170331') 
		   AND TH020='Y' 
		   AND A.TH001 IN ('2301','2302','2304','2308','2309','2331','2332','2334','2339')
         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 inner join
		       INVMB B on E.TJ004=B.MB001 inner join
			   COPTI F on E.TJ001=F.TI001 AND E.TJ002=F.TI002
         WHERE (F.TI003 BETWEEN '20170101' AND '20170331') 
		   AND E.TJ021 = 'Y' 
		   AND E.TJ001 IN ('2401','2404','2408','2409','2431','2434','2439')  
         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 inner join
		       INVMB B on A.TH004=B.MB001 inner join
			   COPTG C on A.TH001=C.TG001 AND A.TH002=C.TG002
         WHERE (C.TG003 BETWEEN '20170101' AND '20170331') 
		   AND TH020 = 'Y' 
		   AND (TH001='2308') 
        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 inner join
		       INVMB B on A.TH004=B.MB001 inner join 
			   COPTG C on A.TH001=C.TG001 AND A.TH002=C.TG002
         WHERE (C.TG003 BETWEEN '20170101' AND '20170331') 
		   AND TH020 = 'Y' 
		   AND TH001 IN ('2303','2333') 
         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
登入發表回應

我要發表回答

立即登入回答
iThome鐵人賽
參賽組數
1123 組
團體組數
52 組
累計文章數
23096 篇
完賽人數
656 人
iT+看影片追技術 看影片追技術 看更多
熱門tag 看更多
15th鐵人賽 13th鐵人賽 14th鐵人賽 12th鐵人賽 11th鐵人賽 鐵人賽 2019鐵人賽 2018鐵人賽 javascript 2017鐵人賽 python windows php c# windows server linux css react 程式設計 vue.js
熱門問題
熱門回答
熱門文章
IT邦幫忙