Microsoft SQL Server 如何生成连续的数字/字符/时间序列

文章目录

        使用系统视图生成序列
            生成一个连接的数字序列
            生成一个间隔的数字序列
            生成一个连续的字符序列
            生成一个间隔的时间序列
        使用通用表表达式生成序列
            生成一个等差数字序列
            生成一个等比数字序列
            生成斐波那契数列
            生成一个连续的字符序列
            生成一个间隔的时间序列
        使用表值函数生成序列
            创建模拟的 generate_series 表值函数
            使用 generate_series 函数生成序列

有时候我们需要在数据库中生成一些连续的数字、字符或者时间序列值，今天我们继续介绍如何在 Microsoft SQL Server 中实现这个功能。

    📝如果你使用的是 MySQL 数据库，实现相同的功能可以参考这篇文章。
    📝如果你使用的是 Oracle 数据库，实现相同的功能可以参考这篇文章。
    📝如果你使用的是 PostgreSQL ，实现相同的功能可以参考这篇文章。

使用系统视图生成序列

Microsoft SQL Server 中的系统视图，例如 master.dbo.spt_values，可以用于生成数字序列。
生成一个连接的数字序列

以下语句使用视图 master.dbo.spt_values 生成了一个连续的数字序列：

select number
from master.dbo.spt_values
where type = 'P'
and number between 11 and 15;
number|
------|
    11|
    12|
    13|
    14|
    15|



这种方法可以生成从 0 到 2047 之间的连续数字序列。

为了生成更大范围的数字序列，我们可以基于该视图创建一个新的视图，例如：

create view generator
as
select t1.number * 2048 + t2.number as number
from master.dbo.spt_values t1
cross join master.dbo.spt_values t2
where t1.type = 'P' and t2.type = 'P';



视图 generator 可以生成 0 到 4194303 之间的数字序列：

select min(number) as min, max(number) as max, count(number) as count
from generator;
min|max    |count  |
---|-------|-------|
  0|4194303|4194304|


生成一个间隔的数字序列

求余运算符（%）可以用于返回指定增量的间隔数字序列，例如：

select number
from master.dbo.spt_values
where type = 'P'
and number between 1 and 10
and number % 3 = 1;
number|
------|
     1|
     4|
     7|
    10|


该查询返回了一个从 1 到 10 之间、增量为 3 的数字序列。

以下查询返回了一个增量为 -2.5、范围从 15 到 1.4 之间的降序数字序列：

select 15 + number * -2.5 as number
from master.dbo.spt_values
where type = 'P'
and 15 + number * -2.5 >= 1.4;
number|
------|
  15.0|
  12.5|
  10.0|
   7.5|
   5.0|
   2.5|


生成一个连续的字符序列

基于上面的视图和 char(n) 函数可以生成连续的字符序列。例如：

select char(number) as letter
from master.dbo.spt_values
where type = 'P'
and number between 65 and 70;
letter|
------|
A     |
B     |
C     |
D     |
E     |
F     |


该查询返回了字符 A 到 F 的序列，char(n) 函数用于将 ASCII 编码转化为相应的字符。
生成一个间隔的时间序列

更多请见：http://www.mark-to-win.com/tutorial/51511.html