如何获取过去12个滚动月（不含当月）的月度订单数（含零值）

Got it, let's fix your query to show all 12 rolling months (even those with no orders) and turn those counts into a clean comma-separated list. Here's how to do it step by step:

Core Idea #

The key problem is that your original query only returns months with existing orders. To fix this, we first generate a full list of the 12 target months, then left-join that list with your order count data. Finally, we use SQL's XML functions to concatenate the counts into a single string.

Full Working Query #

WITH MonthsCTE AS (
    -- Generate all 12 required months in YearMonth format
    SELECT 
        YEAR(DATEADD(mm, -n, DATEADD(mm, DATEDIFF(mm, 0, GETUTCDATE()), 0))) * 100 + 
        MONTH(DATEADD(mm, -n, DATEADD(mm, DATEDIFF(mm, 0, GETUTCDATE()), 0))) AS YearMonth
    FROM (
        -- Get 12 sequential numbers (1 to 12) using system columns
        SELECT TOP 12 ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS n
        FROM sys.all_columns
    ) AS Numbers
)
-- Create the comma-separated list
SELECT STUFF(
    (
        SELECT ',' + CAST(ISNULL(o.OrderCount, 0) AS VARCHAR(10))
        FROM MonthsCTE m
        LEFT JOIN (
            -- Your original order count logic
            SELECT 
                YEAR(CreatedOn)*100 + MONTH(CreatedOn) AS YearMonth,
                COUNT(*) AS OrderCount
            FROM Orders
            WHERE DATEDIFF(MM, CreatedOn, GETUTCDATE()) BETWEEN 1 AND 12
            GROUP BY YEAR(CreatedOn), MONTH(CreatedOn)
        ) o ON m.YearMonth = o.YearMonth
        ORDER BY m.YearMonth -- Keep months in chronological order
        FOR XML PATH(''), TYPE
    ).value('.', 'NVARCHAR(MAX)'), 1, 1, '' -- Remove the leading comma
) AS OrderCountsList;

Breakdown of Each Part #

1. MonthsCTE: This generates 12 rows, each representing a YearMonth value for the past 12 months (excluding the current month). The DATEADD logic ensures we get the first day of each target month, then convert it to your YYYYMM format.
2. Left Join: By joining our full month list with the order counts, we guarantee every month appears in the result. ISNULL(o.OrderCount, 0) replaces NULL values (months with no orders) with 0.
3. STUFF + FOR XML PATH: This part concatenates all the order count values into a single string. STUFF removes the leading comma that the XML concatenation would otherwise add.

Alternative Recursive CTE (If You Don't Want to Use sys.all_columns) #

If you prefer not to rely on system tables, use a recursive CTE to generate the months:

WITH MonthsCTE AS (
    SELECT 
        YEAR(DATEADD(mm, -12, DATEADD(mm, DATEDIFF(mm, 0, GETUTCDATE()), 0))) * 100 + 
        MONTH(DATEADD(mm, -12, DATEADD(mm, DATEDIFF(mm, 0, GETUTCDATE()), 0))) AS YearMonth,
        1 AS MonthNumber
    UNION ALL
    SELECT 
        YEAR(DATEADD(mm, MonthNumber - 11, DATEADD(mm, DATEDIFF(mm, 0, GETUTCDATE()), 0))) * 100 + 
        MONTH(DATEADD(mm, MonthNumber - 11, DATEADD(mm, DATEDIFF(mm, 0, GETUTCDATE()), 0))) AS YearMonth,
        MonthNumber + 1
    FROM MonthsCTE
    WHERE MonthNumber < 12
)
-- Use the same STUFF/FOR XML PATH code as above

This will output a single string with 12 comma-separated values, one for each of the past 12 months (including zeros where there were no orders).

内容的提问来源于stack exchange，提问作者navigator