如何在MS SQL Server临时表中补全缺失年份并填充0销售额

要解决连续缺失年份的补全问题，LAG()函数确实不太适用——它只能获取前一行数据，没法生成中间缺失的年份。更靠谱的思路是先为每个客户生成完整的年份序列，再和原表关联填充销售额。

方法一：使用递归CTE（适用于MySQL 8+、PostgreSQL、SQL Server等支持递归的数据库） #

这个方法会先锁定每个客户的销售年份范围，再递归生成中间所有年份，最后左连接原表补全销售额为0。

-- 1. 统计每个客户的最早/最晚销售年份
WITH ClientYearRanges AS (
    SELECT 
        Client_ID,
        MIN(SalesYear) AS MinYear,
        MAX(SalesYear) AS MaxYear
    FROM SalesTable
    GROUP BY Client_ID
),
-- 2. 递归生成每个客户的完整年份序列
ClientFullYears AS (
    SELECT 
        Client_ID,
        MinYear AS SalesYear
    FROM ClientYearRanges
    UNION ALL
    SELECT 
        c.Client_ID,
        cy.SalesYear + 1
    FROM ClientFullYears cy
    JOIN ClientYearRanges c ON cy.Client_ID = c.Client_ID
    WHERE cy.SalesYear + 1 <= c.MaxYear
)
-- 3. 左连接原表，缺失销售额填0
SELECT 
    f.Client_ID,
    f.SalesYear,
    COALESCE(s.Sales, 0) AS Sales
FROM ClientFullYears f
LEFT JOIN SalesTable s ON f.Client_ID = s.Client_ID AND f.SalesYear = s.SalesYear
ORDER BY f.Client_ID, f.SalesYear;

代码说明： #

• ClientYearRanges：确定每个客户需要补全年份的起止范围；
• ClientFullYears：通过递归逻辑，从最小年份开始逐年递增，生成该客户的完整年份列表；
• COALESCE()：将左连接后缺失的销售额值替换为0。

方法二：使用数字辅助表（适用于所有SQL数据库） #

如果你的数据库不支持递归CTE，可以先创建一个包含连续年份的辅助表，再关联客户与年份。

-- 先创建年份辅助表（可根据实际业务调整年份范围）
CREATE TABLE YearList (Year INT);
INSERT INTO YearList VALUES (2010), (2011), (2012), (2013), (2014), (2015), (2016);

-- 关联客户与年份，补全销售额
SELECT 
    c.Client_ID,
    y.Year AS SalesYear,
    COALESCE(s.Sales, 0) AS Sales
FROM (SELECT DISTINCT Client_ID FROM SalesTable) c
CROSS JOIN YearList y
LEFT JOIN SalesTable s ON c.Client_ID = s.Client_ID AND y.Year = s.SalesYear
-- 过滤出每个客户的有效年份范围
WHERE y.Year BETWEEN (SELECT MIN(SalesYear) FROM SalesTable WHERE Client_ID = c.Client_ID)
                  AND (SELECT MAX(SalesYear) FROM SalesTable WHERE Client_ID = c.Client_ID)
ORDER BY c.Client_ID, y.Year;

代码说明： #

• CROSS JOIN：将每个客户与所有年份进行组合；
• WHERE子句：过滤掉超出该客户销售年份范围的无效数据；
• 同样用COALESCE()填充缺失的销售额为0。

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