巧用spt_values解决SQL中的连续日期问题

spt_values是什么

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

select * from master..spt_values

 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

 生成每月的每天

我们只需要指定开始和结束日期，就可以生成该日期段的连续天了，这里从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')

生成每年的每月

我们只需要指定开始和结束月份，就可以生成该月份段的连续月了，这里从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;

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