如何基于另一表的上下界在SQLite中生成值序列？

问题场景 #

可以使用SQLite内置的generate_series函数创建固定上下界的数值序列：

sqlite> select value from generate_series(1, 5);
1
2
3
4
5

但如果上下界需要基于某张表中的动态值，例如创建如下temp表：

sqlite> create table temp(
......>     num integer not null
......> );
sqlite> insert into temp values (1), (5);
sqlite> select * from temp;
1
5

直接将子查询作为generate_series的参数会触发语法解析错误：

sqlite> select value from generate_series(
......>     select min(num) from temp,
......>     select max(num) from temp
......> );

需要实现不使用递归CTE的解决方案，实际场景是基于数据集的最小和最大日期生成日期序列（整数序列生成后可轻松转换为日期）。

解决方案：交叉连接动态获取边界 #

通过交叉连接先获取表中的最小和最大值，再传入generate_series作为参数，SQL代码如下：

select gs.value
from (select min(num) as min_val, max(num) as max_val from temp) as bounds
cross join generate_series(bounds.min_val, bounds.max_val) as gs;

执行后会输出预期的序列：

1
2
3
4
5

原理说明 #

• 子查询(select min(num) as min_val, max(num) as max_val from temp)先计算出动态的上下边界，生成仅含一条记录的临时结果集bounds
• 交叉连接将bounds与generate_series关联，此时generate_series可以直接引用bounds中的字段作为参数，规避了直接传入子查询的语法限制

扩展：生成日期序列 #

如果需要生成日期序列，可借助儒略日（Julian Day）将日期转换为整数计算差值，再转换回日期。假设存在存储日期的date_table，字段为record_date：

select date(min(record_date), gs.value || ' days') as seq_date
from (
    select min(record_date) as min_date, julianday(max(record_date)) - julianday(min(record_date)) as day_diff
    from date_table
) as bounds
cross join generate_series(0, bounds.day_diff) as gs;

这段代码会生成从表中最小日期到最大日期的所有连续日期。

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