SQL中生成连续DaysToGame值的额外行实现方案咨询

需要生成重复记录，仅DaysToGame列逐次减1，其余字段值保持不变，直到遇到下一个唯一的DaysToGame值后重启该过程。例如现有DaysToGame=26且LeftToSell=23的行，需补全DaysToGame从25到12的行（每行LeftToSell均为23）；到DaysToGame=11时，再补全DaysToGame从10到9的行（LeftToSell为21），以此类推。

尝试用lag和coalesce函数未达到预期效果，原查询语句如下：

SELECT DISTINCT
    EVENTNAME,
    B.DEFAULTPRICECODE,
    DATEDIFF(DAY,SALEDATE,EVENTDATE) AS DaysToGame,
    COUNT(PURCHASEPRICE) OVER (PARTITION BY EVENTNAME, B.DEFAULTPRICECODE ORDER BY DATEDIFF(DAY,SALEDATE,EVENTDATE) ASC) AS LeftToSell

原查询结果集：

EVENTNAME DEFAULTPRICECODE     DAYSTOGAME      LEFTTOSELL
21S1211A         C                 26              23
21S1211A         C                 11              21
21S1211A         C                  8              20
21S1211A         C                  1              18
21S1211A         C                  0               8

以下以SQL Server为例，通过递归CTE实现需求，MySQL 8.0+、PostgreSQL等支持递归CTE的数据库语法类似：

-- 1. 构建基础数据，获取每个DaysToGame对应的下一个目标天数
WITH base_data AS (
    SELECT 
        EVENTNAME,
        DEFAULTPRICECODE,
        DaysToGame,
        LeftToSell,
        -- 取当前行之后的下一个DaysToGame，作为当前区间的结束边界
        LEAD(DaysToGame, 1, -1) OVER (PARTITION BY EVENTNAME, DEFAULTPRICECODE ORDER BY DaysToGame DESC) AS next_days
    FROM (
        -- 替换为你的原查询（去掉DISTINCT，原结果已按DaysToGame唯一分组）
        SELECT 
            EVENTNAME,
            B.DEFAULTPRICECODE,
            DATEDIFF(DAY,SALEDATE,EVENTDATE) AS DaysToGame,
            COUNT(PURCHASEPRICE) OVER (PARTITION BY EVENTNAME, B.DEFAULTPRICECODE ORDER BY DATEDIFF(DAY,SALEDATE,EVENTDATE) ASC) AS LeftToSell
        FROM your_table B -- 补充你的表名和WHERE条件
    ) t
),
-- 2. 递归生成补全的记录
recursive_data AS (
    -- 初始数据集：原查询的结果
    SELECT 
        EVENTNAME,
        DEFAULTPRICECODE,
        DaysToGame,
        LeftToSell,
        next_days
    FROM base_data

    UNION ALL

    -- 递归生成DaysToGame逐次减1的记录，直到达到下一个边界的前一天
    SELECT 
        r.EVENTNAME,
        r.DEFAULTPRICECODE,
        r.DaysToGame - 1 AS DaysToGame,
        r.LeftToSell,
        r.next_days
    FROM recursive_data r
    WHERE r.DaysToGame - 1 > r.next_days
)
-- 输出最终结果，按天数降序排列
SELECT EVENTNAME, DEFAULTPRICECODE, DaysToGame, LeftToSell
FROM recursive_data
ORDER BY EVENTNAME, DEFAULTPRICECODE, DaysToGame DESC;

逻辑说明： #

1. base_data中，LEAD函数获取当前行之后的下一个DaysToGame值，比如DaysToGame=26对应的next_days=11，意味着需要生成25到12的所有天数。
2. 递归部分从原数据出发，每次生成DaysToGame-1的记录，直到DaysToGame-1不大于next_days时停止，刚好补全两个相邻原记录之间的所有天数。

适配不支持递归CTE的数据库（如MySQL 5.x）： #

可以先生成一个包含0到最大DaysToGame的数字序列表，再通过关联筛选出需要补全的天数：

-- 示例：用数字表关联补全（需预先创建或生成足够多的数字序列）
WITH numbers AS (
    SELECT 0 AS n UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL 
    SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL
    SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL
    SELECT 9 UNION ALL SELECT 10 UNION ALL SELECT 11 UNION ALL
    SELECT 12 UNION ALL SELECT 13 UNION ALL SELECT 14 UNION ALL
    SELECT 15 UNION ALL SELECT 16 UNION ALL SELECT 17 UNION ALL
    SELECT 18 UNION ALL SELECT 19 UNION ALL SELECT 20 UNION ALL
    SELECT 21 UNION ALL SELECT 22 UNION ALL SELECT 23 UNION ALL
    SELECT 24 UNION ALL SELECT 25 UNION ALL SELECT 26
),
base_data AS (
    SELECT 
        EVENTNAME,
        DEFAULTPRICECODE,
        DaysToGame,
        LeftToSell,
        LEAD(DaysToGame,1,-1) OVER (PARTITION BY EVENTNAME, DEFAULTPRICECODE ORDER BY DaysToGame DESC) AS next_days
    FROM (
        -- 你的原查询
        SELECT 
            EVENTNAME,
            B.DEFAULTPRICECODE,
            DATEDIFF(DAY,SALEDATE,EVENTDATE) AS DaysToGame,
            COUNT(PURCHASEPRICE) OVER (PARTITION BY EVENTNAME, B.DEFAULTPRICECODE ORDER BY DATEDIFF(DAY,SALEDATE,EVENTDATE) ASC) AS LeftToSell
        FROM your_table B
    ) t
)
SELECT 
    b.EVENTNAME,
    b.DEFAULTPRICECODE,
    n.n AS DaysToGame,
    b.LeftToSell
FROM base_data b
JOIN numbers n ON n.n BETWEEN b.next_days + 1 AND b.DaysToGame
ORDER BY b.EVENTNAME, b.DEFAULTPRICECODE, n.n DESC;

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