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

spt_values是什么

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


select * from master..spt_values


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

结果为:

巧用spt_values解决SQL中的连续日期问题  (记录较多,只截取部分记录)

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

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


结果为:

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

(记录较多,只截取部分记录)

我们经常使用的就是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

结果为:

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

(完整的有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')


结果为:

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

(完整的有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解决SQL中的连续日期问题


spt_values应用实例

有如下一张表Test

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

要求:显示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解决SQL中的连续日期问题

(完整的有31条记录,这里只截取前几条)

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


上一篇:手把手教你MySQL主从复制,配置不成功你找我!(三)


下一篇:手把手教你MySQL主从复制,配置不成功你找我!(一)