SQL Server：基于枚举日期从现有记录生成新记录

嘿，这个需求我太熟悉了——把单条记录根据units值拆分成多条重复的transaction_dt记录，而且要服务器端高性能实现对吧？不同主流数据库都有对应的最优方案，我给你梳理清楚：

PostgreSQL #

PostgreSQL自带的generate_series是处理这种场景的神器，它是内置的集合生成函数，完全基于集合操作，性能比自定义循环高几个量级：

SELECT 
    t.id,
    t.transaction_dt
FROM 
    your_table t
JOIN 
    generate_series(1, t.units) AS s
ON true
WHERE t.units > 0; -- 过滤掉units为0的无效记录

原理很简单：generate_series(1, t.units)会为每条原记录生成一个从1到units的数字序列，通过JOIN关联后，自然就把原记录复制成units条。

MySQL 8.0+ #

MySQL 8.0及以上支持递归CTE，不用提前建表就能动态生成序列；如果你的units最大值固定且不大，提前建个数字辅助表会更高效：

递归CTE方案（动态生成序列） #

WITH RECURSIVE num_seq AS (
    SELECT 1 AS n
    UNION ALL
    SELECT n + 1 FROM num_seq WHERE n < (SELECT MAX(units) FROM your_table)
)
SELECT 
    t.id,
    t.transaction_dt
FROM 
    your_table t
JOIN 
    num_seq s ON s.n <= t.units
WHERE t.units > 0;

数字辅助表方案（性能最优） #

先创建一次辅助表（数值范围覆盖你的最大units即可）：

CREATE TABLE numbers (n INT PRIMARY KEY AUTO_INCREMENT);
-- 插入足够多的数值，比如插入1到10000
INSERT INTO numbers VALUES (),(),(),(),(),(),(),(),(),(); -- 重复执行直到满足需求

之后查询直接关联，速度极快：

SELECT 
    t.id,
    t.transaction_dt
FROM 
    your_table t
JOIN 
    numbers s ON s.n <= t.units
WHERE t.units > 0;

SQL Server #

SQL Server 2022+版本支持GENERATE_SERIES，旧版本可以用递归CTE：

2022+版本方案 #

SELECT 
    t.id,
    t.transaction_dt
FROM 
    your_table t
CROSS APPLY 
    GENERATE_SERIES(1, t.units) s
WHERE t.units > 0;

旧版本递归CTE方案 #

WITH num_seq AS (
    SELECT 1 AS n
    UNION ALL
    SELECT n + 1 FROM num_seq WHERE n < (SELECT MAX(units) FROM your_table)
)
SELECT 
    t.id,
    t.transaction_dt
FROM 
    your_table t
JOIN 
    num_seq s ON s.n <= t.units
WHERE t.units > 0
OPTION (MAXRECURSION 0); -- 如果units最大值超过100，必须加这个选项关闭递归限制

通用性能优化Tips #

• 先过滤：一定要加上WHERE t.units > 0，避免处理无效记录，减少JOIN的数据量。
• 优先用辅助表：如果units的最大值固定，数字辅助表是性能天花板，因为它是带索引的物理表，JOIN效率极高。
• 避开行级操作：绝对不要用游标、自定义循环这类逐行处理的方式，集合式的JOIN操作才是数据库的性能优势所在。

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