個人中心
退出
Microsoft SQL Server 如何生成連續(xù)的數(shù)字/字符/時間序列
作者: 不剪發(fā)的Tony老師
畢業(yè)于北京航空航天大學(xué)，十多年數(shù)據(jù)庫管理與開發(fā)經(jīng)驗(yàn)，目前在一家全球性的金融公司從事數(shù)據(jù)庫架構(gòu)設(shè)計(jì)。CSDN學(xué)院簽約講師以及GitChat專欄作者。csdn上的博客收藏于以下地址：https://tonydong.blog.csdn.net
文章目錄
使用系統(tǒng)視圖生成序列
生成一個連接的數(shù)字序列
生成一個間隔的數(shù)字序列
生成一個連續(xù)的字符序列
生成一個間隔的時間序列
使用通用表表達(dá)式生成序列
生成一個等差數(shù)字序列
生成一個等比數(shù)字序列
生成斐波那契數(shù)列
生成一個連續(xù)的字符序列
生成一個間隔的時間序列
使用表值函數(shù)生成序列
創(chuàng)建模擬的 generate_series 表值函數(shù)
使用 generate_series 函數(shù)生成序列
大家好，我是只談技術(shù)不剪發(fā)的 Tony 老師。有時候我們需要在數(shù)據(jù)庫中生成一些連續(xù)的數(shù)字、字符或者時間序列值，今天我們繼續(xù)介紹如何在 Microsoft SQL Server 中實(shí)現(xiàn)這個功能。
??如果你使用的是 MySQL 數(shù)據(jù)庫，實(shí)現(xiàn)相同的功能可以參考這篇文章。
??如果你使用的是 Oracle 數(shù)據(jù)庫，實(shí)現(xiàn)相同的功能可以參考這篇文章。
??如果你使用的是 PostgreSQL ，實(shí)現(xiàn)相同的功能可以參考這篇文章。
使用系統(tǒng)視圖生成序列
Microsoft SQL Server 中的系統(tǒng)視圖，例如 master.dbo.spt_values，可以用于生成數(shù)字序列。
生成一個連接的數(shù)字序列
以下語句使用視圖 master.dbo.spt_values 生成了一個連續(xù)的數(shù)字序列：
select number
from master.dbo.spt_values
where type = 'P'
and number between 11 and 15;
number|
------|
11|
12|
13|
14|
15|
這種方法可以生成從 0 到 2047 之間的連續(xù)數(shù)字序列。
為了生成更大范圍的數(shù)字序列，我們可以基于該視圖創(chuàng)建一個新的視圖，例如：
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 之間的數(shù)字序列：
select min(number) as min, max(number) as max, count(number) as count
from generator;
min|max |count |
---|-------|-------|
0|4194303|4194304|
生成一個間隔的數(shù)字序列
求余運(yùn)算符（%）可以用于返回指定增量的間隔數(shù)字序列，例如：
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 的數(shù)字序列。
以下查詢返回了一個增量為 -2.5、范圍從 15 到 1.4 之間的降序數(shù)字序列：
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|
生成一個連續(xù)的字符序列
基于上面的視圖和 char(n) 函數(shù)可以生成連續(xù)的字符序列。例如：
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) 函數(shù)用于將 ASCII 編碼轉(zhuǎn)化為相應(yīng)的字符。
生成一個間隔的時間序列
同樣基于上面的視圖和時間加減法可以生成間隔的時間序列。例如：
select dateadd(hour, number, '2020-01-01 00:00:00') as ts
from master.dbo.spt_values
where type = 'P'
and number between 0 and 12;
ts |
-------------------|
2020-01-01 00:00:00|
2020-01-01 01:00:00|
2020-01-01 02:00:00|
2020-01-01 03:00:00|
2020-01-01 04:00:00|
2020-01-01 05:00:00|
2020-01-01 06:00:00|
2020-01-01 07:00:00|
2020-01-01 08:00:00|
2020-01-01 09:00:00|
2020-01-01 10:00:00|
2020-01-01 11:00:00|
2020-01-01 12:00:00|
該查詢返回了 2020-01-01 00:00:00 到 2020-01-01 12:00:00、間隔為 1 小時的所有時間點(diǎn)。
使用通用表表達(dá)式生成序列
生成一個等差數(shù)字序列
通用表表達(dá)式（Common Table Expression）的遞歸調(diào)用可以用于生成各種數(shù)列。例如：
with t(n) as (
select 0
union all
select n+2 from t where n < 10
)
select n from t;
n |
--|
0|
2|
4|
6|
8|
10|
以上語句生成了一個 0 到 10之間、增量為 2 的偶數(shù)數(shù)列，執(zhí)行過程如下：
首先，執(zhí)行 CTE 中的初始化查詢，生成一行數(shù)據(jù)（0）；
然后，第一次執(zhí)行遞歸查詢，判斷 n < 10，生成一行數(shù)據(jù) 2（n+2）；
接著，重復(fù)執(zhí)行遞歸查詢，生成更多的數(shù)據(jù)；直到 n = 10 時不滿足條件終止遞歸；此時臨時表 t 中包含 6 條數(shù)據(jù)；
最后，執(zhí)行主查詢，返回所有的數(shù)據(jù)。
生成一個等比數(shù)字序列
上文中的系統(tǒng)視圖只能用于生成等差數(shù)列，通用表表達(dá)式則可以生成更復(fù)雜的數(shù)列，例如等比數(shù)列：
with t(n) as (
select 1
union all
select n*4 from t where n*4 < 1000
)
select n from t;
n |
---|
1|
4|
16|
64|
256|
從第二行開始，每個數(shù)字都是上一行的 4 倍。
生成斐波那契數(shù)列
斐波那契數(shù)列（Fibonacci series）是指從數(shù)字 0 和 1（或者從 1 和 1）開始，后面的每個數(shù)字等于它前面兩個數(shù)字之和（0、1、1、2、3、5、8、13、21、…）。使用通用表表達(dá)式可以很容易地生成斐波那契數(shù)列：
with fibonacci (n, fib_n, next_fib_n) as (
select 1, 0, 1
union all
select n + 1, next_fib_n, fib_n + next_fib_n
from fibonacci
where n < 10 )
select * from fibonacci;
n |fib_n|next_fib_n|
--|-----|----------|
1| 0| 1|
2| 1| 1|
3| 1| 2|
4| 2| 3|
5| 3| 5|
6| 5| 8|
7| 8| 13|
8| 13| 21|
9| 21| 34|
10| 34| 55|
其中，字段 n 表示該行包含了第 n 個斐波那契數(shù)列值；字段 fib_n 表示斐波那契數(shù)列值；字段 next_fib_n 表示下一個斐波那契數(shù)列值。
生成一個連續(xù)的字符序列
基于通用表表達(dá)式和 char(n) 函數(shù)同樣可以生成連續(xù)的字符序列，例如：
with t(n) as (
select 65
union all
select n+1 from t where n < 70
)
select char(n) as letter from t;
letter|
------|
A |
B |
C |
D |
E |
F |
生成一個間隔的時間序列
以下語句使用遞歸通用表表達(dá)式生成一個時間序列：
with ts(v) as (
select cast('2020-01-01 00:00:00' as datetime2)
union all
select dateadd(hour,1, v) from ts where v < cast('2020-01-01 12:00:00' as datetime2)
)
select v from ts;
v |
-------------------|
2020-01-01 00:00:00|
2020-01-01 01:00:00|
2020-01-01 02:00:00|
2020-01-01 03:00:00|
2020-01-01 04:00:00|
2020-01-01 05:00:00|
2020-01-01 06:00:00|
2020-01-01 07:00:00|
2020-01-01 08:00:00|
2020-01-01 09:00:00|
2020-01-01 10:00:00|
2020-01-01 11:00:00|
2020-01-01 12:00:00|
以上查詢返回了 2020-01-01 00:00:00 到 2020-01-01 12:00:00 之間，間隔為 1 小時的所有時間點(diǎn)。
使用表值函數(shù)生成序列
Microsoft SQL Server 支持表值函數(shù)（table-valued function），也就是返回結(jié)果為集合（表）的函數(shù)，可以用于模擬 PostgreSQL 中的 generate_series 函數(shù)。
創(chuàng)建模擬的 generate_series 表值函數(shù)
我們創(chuàng)建一個表值函數(shù) generate_series：
create or alter function generate_series(@pstart numeric(38,10), @pstop numeric(38,10), @pstep numeric(38,10) = 1.0)
returns @generate_series table(n numeric(38,10))
as
begin
if @pstep = 0 return
if @pstart > @pstop and @pstep > 0 return
if @pstart < @pstop and @pstep < 0 return
;with t(n, v) as (
select 1, @pstart
union all
select n+1, cast(@pstart + n * @pstep as numeric(38,10)) from t where n <= floor(abs((@pstop-@pstart)/@pstep))
)
insert into @generate_series
select v from t
return
end;
其中，@pstart 表示數(shù)據(jù)序列的起點(diǎn)，@pstop 表示數(shù)據(jù)序列的終點(diǎn)，@pstep 表示每次的增量，不允許為 0，默認(rèn)為 1。
使用 generate_series 函數(shù)生成序列
創(chuàng)建了 generate_series 函數(shù)之后，我們就可以用它來生成各種序列值。例如：
select cast (n as integer) as n
from generate_series(11, 15, default);
n |
--|
11|
12|
13|
14|
15|
select n from generate_series(15, 1.4, -2.5);
n |
-------------|
15.0000000000|
12.5000000000|
10.0000000000|
7.5000000000|
5.0000000000|
2.5000000000|
第一個函數(shù)返回了 11 到 15 的連續(xù)整數(shù)；第二個函數(shù)返回了 15 到 1.4 之間增量為 -2.5 的數(shù)字序列。
我們同樣可以使用 generate_series 函數(shù)生成字符序列和時間序列：
select char(n) as letter
from generate_series(65, 70, 1);
letter|
------|
A |
B |
C |
D |
E |
F |
select dateadd(hour, n, timefromparts(0,0,0,0,0)) as time
from generate_series(0, 12, default);
time |
--------|
00:00:00|
01:00:00|
02:00:00|
03:00:00|
04:00:00|
05:00:00|
06:00:00|
07:00:00|
08:00:00|
09:00:00|
10:00:00|
11:00:00|
12:00:00|
如果覺得文章對你有用，歡迎關(guān)注??、評論??、點(diǎn)贊贊??
分類導(dǎo)航