作者:软装之家888 | 来源:互联网 | 2023-08-27 12:38
这是我的查询。但是我收到错误消息:
第15级状态1行1的消息102
'.2017'附近的语法不正确。
第15级状态1行4的消息102
'.2017'附近的语法不正确。
第15级,州1,第15行的消息102
“ MYAC”附近的语法不正确。
第15层状态1州第21行的消息102
“ BSUM”附近的语法不正确。
图片:
SELECT
MakineListe.MakineAdi,IFNULL(MYABC.2017,0) AS '2017',IFNULL(MYABC.2018,0) AS '2018',IFNULL(MYABC.2019,0) AS '2019',IFNULL(MYABC.Stok,0) AS Stok
FROM
MakineListe
LEFT JOIN
(SELECT
MYAC.ParcaMakineAdi,MYAC.2017 AS '2017',MYAC.2018 AS '2018',MYAC.2019 AS '2019',BSUM.TOTAL as Stok
FROM
(SELECT
MakinelerVeParcalar.ParcaMakineAdi,SUM(CASE WHEN YEAR(Siparisler.SiparisTarihi) = 2017 THEN MakinelerVeParcalar.Miktar ELSE 0 END) AS '2017',SUM(CASE WHEN YEAR(Siparisler.SiparisTarihi) = 2018 THEN MakinelerVeParcalar.Miktar ELSE 0 END) AS '2018',SUM(CASE WHEN YEAR(Siparisler.SiparisTarihi) = 2019 THEN MakinelerVeParcalar.Miktar ELSE 0 END) AS '2019'
FROM
MakinelerVeParcalar
LEFT JOIN
Siparisler ON Siparisler.IsEmriNo = MakinelerVeParcalar.IsEmriNo
GROUP BY
MakinelerVeParcalar.ParcaMakineAdi) MYAC
LEFT JOIN
(SELECT
ParcaMakineAdi,SUM(Stok.Miktar) AS TOTAL
FROM
Stok
GROUP BY
ParcaMakineAdi) BSUM ON BSUM.ParcaMakineAdi = MYAC.ParcaMakineAdi
) MYABC ON MakineListe.MakineAdi = MYABC.ParcaMakineAdi
ORDER BY
MakineListesi.MakineAdi;
查询现在可以。谢谢...
select MakineListe.MakineAdi,NULLIF(MYABC.[2017],0) AS '2017',NULLIF(MYABC.[2018],0) AS '2018',NULLIF(MYABC.[2019],0) AS '2019',NULLIF(MYABC.[Stok],0) as Stok from MakineListe
left join
(
SELECT MYAC.ParcaMakineAdi,MYAC.[2017] AS '2017',MYAC.[2018] AS '2018',MYAC.[2019] AS '2019',BSUM.TOTAL as Stok
FROM
(
SELECT
MakinelerVeParcalar.ParcaMakineAdi,SUM(case when YEAR(Siparisler.SiparisTarihi) = 2017 then MakinelerVeParcalar.Miktar else 0 end) as '2017',SUM(case when YEAR(Siparisler.SiparisTarihi) = 2018 then MakinelerVeParcalar.Miktar else 0 end) AS '2018',SUM(case when YEAR(Siparisler.SiparisTarihi) = 2019 then MakinelerVeParcalar.Miktar else 0 end) AS '2019'
FROM MakinelerVeParcalar
LEFT JOIN Siparisler ON Siparisler.IsEmriNo = MakinelerVeParcalar.IsEmriNo
GROUP BY MakinelerVeParcalar.ParcaMakineAdi
) MYAC
LEFT JOIN
(
SELECT ParcaMakineAdi,SUM(Stok.Miktar) AS TOTAL
FROM Stok
GROUP BY ParcaMakineAdi
) BSUM ON BSUM.ParcaMakineAdi = MYAC.ParcaMakineAdi
) MYABC
on MakineListe.MakineAdi = MYABC.ParcaMakineAdi
ORDER BY MakineAdi