spt_values是什么

spt_values是SQL Server新增的一个系统表，表里面都是一些枚举数据。我们可以通过如下查询语句来查看里面的数据

select * from master..spt_values

(因为该表属于系统数据库master下面，所以通常在表名前面添加库名master)

结果为：

（记录较多，只截取部分记录）

spt_values连续记录 但是通常我们使用的是Type='P'的数据记录，这些记录是一组从0开始，2047为止的连续整数，具体如下：

select * from master..spt_values where type = 'P'

结果为：

（记录较多，只截取部分记录）

我们经常使用的就是 number 列，通过该列我们可以生成很多连续的记录，包括连续的日期，例如每天的24小时，每个月的每天，每年的12个月等等。

生成每天的24小时 我们只需要指定开始和结束时间，就可以生成该时间段的连续小时了，这里从0点到23点。

SELECT

SUBSTRING ( CONVERT ( CHAR ( 32 ),

DATEADD (HH, number , CONCAT ( '2021-01-05' , ' ' , '00:00' )), 120 ), 1 , 16 ) AS GroupDay

FROM

master..spt_values

WHERE TYPE = 'P'

AND DATEDIFF (HH, DATEADD (HH, number ,

CONCAT ( '2021-01-05' , ' ' , '00:00' )),

CONCAT ( '2021-01-05' , ' ' , '23:00' ))>= 0

结果为：

（完整的有24条记录，这里只截取前几条）

生成每月的每天

我们只需要指定开始和结束日期，就可以生成该日期段的连续天了，这里从1月1日到1月31日。

SELECT

CONVERT ( NVARCHAR ( 10 ), DATEADD ( DAY , number , '2021-01-01' ), 120 ) AS GroupDay

FROM

master..spt_values

WHERE TYPE = 'P'

AND number <= DATEDIFF ( DAY , '2021-01-01' , '2021-01-31' )

结果为：

（完整的有31条记录，这里只截取前几条）

生成每年的每月

我们只需要指定开始和结束月份，就可以生成该月份段的连续月了，这里从1月到12月。

SELECT

SUBSTRING ( CONVERT ( NVARCHAR ( 10 ), DATEADD ( MONTH , number , '2021-01-01' ), 120 ), 1 , 7 ) AS GroupMonth

FROM

master..spt_values

WHERE TYPE = 'P'

AND number <= DATEDIFF ( MONTH , '2021-01-01' , '2021-12-01' )

结果为：

spt_values 应用实例

有如下一张表Test

要求：显示1月份所有日期的DataValue值，如果没有值的，就显示为0。
分析：我们数据库中只存储了4条数据，这时候我们可以利用SQL的表spt_values来实现。
解法：

SELECT DATEADD ( DAY , number , CONVERT (DATETIME, '2021-01-01' )) [DataTime],

ISNULL (DataValue, 0 ) DataValue

FROM master..spt_values

LEFT JOIN Test

ON DATEADD ( DAY , number , CONVERT (DATETIME, '2021-01-01' )) = [DataTime]

WHERE type = 'P'

AND number

BETWEEN 0 AND DATEDIFF ( DAY , '2021-01-01' , DATEADD ( MONTH , 1 , '2021-01-01' )) -1 ;

结果为：

（完整的有31条记录，这里只截取前几条）

以上就是spt_values的一些用法，当然它不止在连续日期上的应用，只要是连续数字的问题，均可关联spt_values来解决。