如何通过Outer Join与Group实现按客户展示全月份采购数据？

没问题，我来帮你搞定这个需求！你之前的思路是对的——用日历表补全缺失月份，但关联方式出了问题。核心问题在于：你需要先生成每个客户 + 每个日历月份的完整组合，再关联采购数据，而不是直接用采购表和日历表关联。

核心思路 #

1. 先通过CROSS JOIN把Customers和日历表（@Cal）组合，得到所有客户对应所有目标月份的记录（这是补全缺失月份的关键）；
2. 用LEFT JOIN关联Purchases表，匹配对应的客户、年份和月份；
3. 用ISNULL(SUM(P.Amt), 0)把无采购时的NULL转换成0，最后按客户和月份分组统计。

正确的SQL查询 #

SELECT 
    C.CustomerID,
    C.CustomerName,
    L.CalYear AS [Year],
    L.CalMonth AS [Month],
    ISNULL(SUM(P.Amt), 0) AS Amt
FROM 
    Customers C
CROSS JOIN 
    @Cal L  -- 生成每个客户与所有日历月份的全组合
LEFT JOIN 
    Purchases P 
    ON C.CustomerID = P.CustomerID
    AND YEAR(P.PurchaseDate) = L.CalYear
    AND MONTH(P.PurchaseDate) = L.CalMonth
GROUP BY 
    C.CustomerID, C.CustomerName, L.CalYear, L.CalMonth
ORDER BY 
    C.CustomerID, L.CalYear DESC, L.CalMonth DESC;  -- 按预期结果排序

关键细节解释 #

• CROSS JOIN @Cal：这一步会为每个客户生成日历表中所有月份的记录，比如3个客户+9个月份会得到27条基础记录，确保没有遗漏任何客户的任何月份；
• LEFT JOIN Purchases：以客户+月份的组合为基础，左连接采购表，这样没有采购的月份会返回NULL；
• ISNULL(SUM(P.Amt), 0)：当某个月份没有采购记录时，SUM(P.Amt)会返回NULL，用ISNULL把它转换成0，符合你的预期；
• GROUP BY的字段：必须基于日历表的CalYear和CalMonth分组，而不是采购表的日期，因为我们是按日历月份来统计的。

进阶：自动生成日历范围（无需手动插入临时表） #

如果不想手动维护@Cal临时表，可以用CTE（公共表表达式）自动生成指定时间段内的所有月份，比如生成2017年8月到2018年4月的月份：

WITH Cal AS (
    SELECT 
        DATEADD(MONTH, n, '2017-08-01') AS CalDate
    FROM 
        (SELECT TOP 9 ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) - 1 AS n 
         FROM sys.objects) AS nums  -- TOP 9对应2017-08到2018-04共9个月
)
SELECT 
    C.CustomerID,
    C.CustomerName,
    YEAR(Cal.CalDate) AS [Year],
    MONTH(Cal.CalDate) AS [Month],
    ISNULL(SUM(P.Amt), 0) AS Amt
FROM 
    Customers C
CROSS JOIN 
    Cal
LEFT JOIN 
    Purchases P 
    ON C.CustomerID = P.CustomerID
    AND YEAR(P.PurchaseDate) = YEAR(Cal.CalDate)
    AND MONTH(P.PurchaseDate) = MONTH(Cal.CalDate)
GROUP BY 
    C.CustomerID, C.CustomerName, YEAR(Cal.CalDate), MONTH(Cal.CalDate)
ORDER BY 
    C.CustomerID, YEAR(Cal.CalDate) DESC, MONTH(Cal.CalDate) DESC;

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