PostgreSQL中为每个客户生成num区间内的多行数据

需求回顾 #

原表数据：

需要保留原记录，同时为每个client_id补充1到num之间所有缺失的数值记录，最终得到包含完整区间的数据集。

方案1：直接插入补充记录到原表 #

假设你的表名为client_data，且id是自增主键（SERIAL或GENERATED AS IDENTITY类型），执行以下SQL即可自动插入所有缺失的记录：

INSERT INTO client_data (client_id, num)
SELECT t.client_id, s.n
FROM client_data t
CROSS JOIN generate_series(1, t.num) s(n)
WHERE NOT EXISTS (
    SELECT 1 
    FROM client_data 
    WHERE client_id = t.client_id AND num = s.n
);

逻辑说明：

1. generate_series(1, t.num)：为每条原记录生成从1到对应num的整数序列
2. CROSS JOIN：将每个client_id与它的数值序列做关联，得到该client需要的所有数值条目
3. NOT EXISTS：过滤掉原表中已存在的(client_id, num)组合，只插入缺失的记录
4. 自增主键id会由数据库自动生成，无需手动指定

方案2：查询完整结果（不修改原表） #

如果只是需要查看完整数据集而不修改原表，可使用以下查询语句：

SELECT 
    row_number() OVER (ORDER BY client_id, num DESC) AS id,
    client_id,
    num
FROM (
    -- 保留原记录
    SELECT client_id, num FROM client_data
    UNION ALL
    -- 生成1到num-1的补充记录（原记录已包含num）
    SELECT t.client_id, s.n
    FROM client_data t
    CROSS JOIN generate_series(1, t.num - 1) s(n)
) AS combined
ORDER BY id;

逻辑说明：

1. UNION ALL合并原记录和补充记录（补充记录只生成1到num-1，避免重复）
2. row_number()生成连续的id，排序规则与示例结果一致

注意事项 #

• 如果num可能为0，需在FROM子句后添加WHERE t.num > 0过滤无效数据
• 建议给表添加唯一约束UNIQUE(client_id, num)，防止重复插入或原表存在重复数据
• 替换SQL中的client_data为你的实际表名

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