SQL怎么用窗口函数和生成表快速生成数据序列

在数据库开发与测试过程中，经常需要快速得到一组连续的数字、日期或者自定义编码序列。传统做法是用多个UNION ALL拼值，或者写递归公用表表达式，当序列长度达到几万、几十万时，语句冗长且性能下降明显。把窗口函数和一张预先生成的物理数字表结合起来，可以用极短的SQL产出任意规模的序列。

一、什么是生成表与窗口函数配合思路

生成表（也称为数字辅助表、Tally Table）是一张只保存连续正整数的物理表，通常字段为id且带有聚集索引。它的作用是为其他查询提供现成的行源，从而避免运行时递归。窗口函数中的ROW_NUMBER()则用来为每一行打上从1开始的序号，但在已有生成表的情况下，我们往往直接读取id而无需再计算行号。

两者结合的典型模式是：用生成表提供基础行数，通过交叉连接（CROSS JOIN）把规模放大，再利用运算符或日期函数把整数转换成目标序列。这种写法把“生成行”和“计算值”分离，执行计划通常只是索引扫描和投影，不会触发递归或排序。

1.1 生成表的结构示例

以SQL Server为例，创建一张最简单的生成表并填充1到100000的整数，代码如下：

-- 创建生成表
CREATE TABLE dbo.Tally (id INT PRIMARY KEY CLUSTERED);
-- 使用窗口函数与交叉连接快速灌入数据
INSERT INTO dbo.Tally (id)
SELECT TOP (100000)
    ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS id
FROM sys.all_columns a
CROSS JOIN sys.all_columns b;

上面的脚本借助系统视图sys.all_columns做交叉连接得到足够多的行，再用ROW_NUMBER()窗口函数生成序号并写入表中。灌表动作只需执行一次，后续所有序列需求都直接查这张表。

在MySQL中不支持TOP，可以用LIMIT和类似思路实现；PostgreSQL则可使用generate_series先建表。无论哪种库，核心都是把“耗时的行生成”提前到 preparation 阶段。

二、用生成表快速产出数字与日期序列

有了生成表之后，取前N个连续数字就是一句SELECT id FROM Tally WHERE id <= N。如果要得到从某个基准日开始的连续日期，只需把整数加上去。

2.1 SQL Server日期序列写法

假设需要2024-01-01开始的90天日期，可以直接用DATEADD：

DECLARE @start DATE = '2024-01-01';
DECLARE @days INT = 90;
SELECT
    DATEADD(DAY, t.id - 1, @start) AS seq_date,
    t.id AS day_no
FROM dbo.Tally t
WHERE t.id <= @days
ORDER BY t.id;

这段查询只扫描生成表的前90行，由于id是聚集索引，读取非常快。输出的seq_date就是连续日期，day_no是序列号。相比递归CTE，它不需要每次迭代都访问自身，执行计划中看不到“Index Spool”或“Recursive”字样。

如果序列要倒序或者带小时粒度，只需把DATEADD的单位换成小时，并调整WHERE条件。因为值是通过算术映射得到的，业务逻辑改动很小。

2.2 MySQL与PostgreSQL示例

MySQL没有DATEADD，但可以用DATE_ADD或者直接在日期上加减间隔：

-- 假设已存在tally表(id INT PRIMARY KEY)
SELECT
    DATE_ADD('2024-01-01', INTERVAL (t.id - 1) DAY) AS seq_date
FROM tally t
WHERE t.id <= 90
ORDER BY t.id;

PostgreSQL则更直观，因为内置了generate_series，但若坚持用生成表，写法如下：

SELECT
    DATE '2024-01-01' + (t.id - 1) AS seq_date
FROM tally t
WHERE t.id <= 90
ORDER BY t.id;

三种数据库的语法差异只在日期函数，整体模式完全一致：过滤生成表、用整数偏移量映射。这样团队成员只要理解一个模型，就能跨库写序列查询。

三、放大序列：交叉连接突破单表上限

如果生成表只存了10万行，而你需要500万行订单号，单表不够用。此时用两张生成表做交叉连接，行数等于两者乘积。

3.1 交叉连接扩量示例

以下SQL Server代码产出最多100万行连续编号：

SELECT TOP (1000000)
    (a.id - 1) * 1000 + b.id AS big_seq
FROM dbo.Tally a
CROSS JOIN dbo.Tally b
WHERE a.id <= 1000 AND b.id <= 1000
ORDER BY big_seq;

这里a和b都是同一张生成表，各取前1000行，交叉后正好100万种组合。通过简单乘法映射成全局唯一序列。由于两表都走索引扫描，没有递归，速度远高于循环插入。

需要注意交叉连接会产生笛卡尔积，若过滤条件写错会爆出海量行，因此建议始终用TOP或LIMIT封顶，并在测试环境先小批量验证。

四、性能对比与避坑建议

我们用三种方式生成100万行数字：递归CTE、循环插入、生成表+窗口函数映射。在普通开发机上的经验耗时大致如下：

生成表方案明显更快，且CPU曲线平稳。避坑方面，第一要确保生成表id是聚集索引，否则排序会溢到tempdb；第二是不要在生成表上建多余列，保持行窄；第三是序列如果用于生产编号，要考虑并发重复问题，可配合SEQUENCE对象而非纯SELECT。

窗口函数在这里的价值，除了建表时一次性生成id，还可以在不使用物理表时直接用ROW_NUMBER虚拟出序列，例如：

-- 无生成表时的轻量写法（适合小序列）
SELECT ROW_NUMBER() OVER (ORDER BY a.id) AS seq
FROM sys.all_objects a
CROSS JOIN sys.all_objects b
WHERE ROW_NUMBER() OVER (ORDER BY a.id) <= 1000;

但这种纯虚拟写法在超大数据量时仍不如物理生成表稳定，因此推荐把“生成表+窗口函数初始化”作为标准实践。

五、总结应用模式

快速生成数据序列的通用模板可以归纳为：先建窄生成表并加聚集索引，用窗口函数一次性填数；业务查询时按需要过滤id，用算术或日期函数映射成目标值；规模不够就用受控的交叉连接放大。该模式在测试数据构造、日历维表、批量订单号预生成等场景都非常实用，且易于移植到主流关系型数据库。

SQL窗口函数生成表修改时间：2026-08-08 04:51:16