问题描述
我有一个查询,我想显示一年中的所有月份,无论该月份是否有销售.我知道问题出在 Group By,但如何更改查询以便我不需要它?
I have a query that I would like to display all months for the year regardless if they have sales for that month or not. I know the issue is with the Group By, but how do I change the query so I don't need it?
SELECT ISNULL(MONTH(sd.SBINDT),0) AS MonthSold, SUM(sd.SBQSHP) AS QtySold FROM dbo.SalesData sd WHERE sd.SBTYPE = 'O' AND sd.SBITEM = @Part AND YEAR(sd.SBINDT) = @Year AND sd.DefaultLocation = @Location GROUP BY MONTH(sd.SBINDT)
推荐答案
试试这个:-
SELECT M.Months AS MonthSold,D.QtySold as QtySold FROM ( SELECT distinct(MONTH(sd.SBINDT))as Months from dbo.SalesData sd)M left join ( SELECT MONTH(sd.SBINDT) AS MonthSold,SUM(sd.SBQSHP) AS QtySold FROM dbo.SalesData sd WHERE sd.SBTYPE = 'O' AND sd.SBITEM = @Part AND YEAR(sd.SBINDT) = @Year AND sd.DefaultLocation = @Location GROUP BY MONTH(sd.SBINDT) )D ON M.Months = D.MonthSold