sql-server - 显示所有月份，即使值为 NULL

Question

我正在制作一个图表，我需要在其中显示一年中的所有月份以显示每月的销售额。到目前为止，我只能显示有相应值的月份。到目前为止，这是我的存储过程查询。

SELECT (DATENAME (MONTH, DATEADD ( MONTH, DATEPART(MONTH, ORDER_DATE), -1) ))
AS MONTH_NAME,
  SUM ([ORDER].NET_AMOUNT) AS TOTAL_SALES
  FROM [ORDER], ORDER_DETAILS
  WHERE [ORDER].ORDER_ID = ORDER_DETAILS.ORDER_ID
  --AND (DATENAME (MONTH, DATEADD ( MONTH, DATEPART(MONTH, ORDER_DATE), -1) )) = (DATENAME (MONTH, DATEADD ( MONTH, DATEPART(MONTH, @Order_month), -1) )) 
  GROUP BY MONTH([ORDER].ORDER_DATE)
  ORDER BY MONTH_NAME

它只显示 1 个月和该月的销售额。有人可以帮我解决这个问题吗？

Answer 1

创建月份表

    Create table #months
    (
    monthid int,
    monthname varchar(100)
    )

    insert into #monthids
    (monthid,monthname)
    values
    (1,'January'),(2,'February')...insert upto 12 months


;with cte
as
(
SELECT 
(DATENAME (MONTH, DATEADD ( MONTH, DATEPART(MONTH, ORDER_DATE), -1) ))
AS MONTH_NAME,
SUM ([ORDER].NET_AMOUNT) AS TOTAL_SALES
FROM [ORDER], ORDER_DETAILS
WHERE [ORDER].ORDER_ID = ORDER_DETAILS.ORDER_ID
(DATENAME (MONTH, DATEADD ( MONTH, DATEPART(MONTH, @Order_month), -1) )) 
GROUP BY MONTH([ORDER].ORDER_DATE)
)
select 
m.Monthname,
isnull(total_Sales,0) as 'totalSales'
from #months m
Left join
cte c
on c.monthname=m.month_name