创建月份表

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

首先请使用正确的JOIN语法和别名。
您可以使用月份创建CTE，使用输出创建CTE，并将它们连接起来：

;WITH mcte AS (
    SELECT CAST('2016-01-01' as datetime) as MONTH_NAME
    UNION ALL
    SELECT DATEADD(MONTH,1,MONTH_NAME)
    FROM mcte
    WHERE DATEPART(MONTH,MONTH_NAME) < 12
), octe AS (
    SELECT  (DATENAME (MONTH, DATEADD ( MONTH, DATEPART(MONTH, ORDER_DATE), -1) )) AS MONTH_NAME,
            SUM (o.NET_AMOUNT) AS TOTAL_SALES
    FROM [ORDER] o
    INNER JOIN ORDER_DETAILS od
        ON o.ORDER_ID = od.ORDER_ID
        --AND (DATENAME (MONTH, DATEADD ( MONTH, DATEPART(MONTH, ORDER_DATE), -1) )) = (DATENAME (MONTH, DATEADD ( MONTH, DATEPART(MONTH, @Order_month), -1) )) 
    GROUP BY MONTH(o.ORDER_DATE)
)

SELECT  DATENAME(MONTH,m.MONTH_NAME) as MONTH_NAME,
        o.TOTAL_SALES
FROM mcte m
LEFT JOIN octe o
    ON o.MONTH_NAME = DATENAME(MONTH,m.MONTH_NAME)

这将给出所有月份名称和所有总销售额，如果没有总销售额-它将显示NULL。

试试这个：

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