当前位置:主页 > 查看内容

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

发布时间:2021-04-19 00:00| 位朋友查看

简介:本文转载自微信公众号「SQL数据库」,作者丶平凡世界 。转载本文请联系开发公众号。 spt_values是什么 spt_values是SQL Server新增的一个系统表,表里面都是一些枚举数据。我们可以通过如下查询语句来查看里面的数据 select * from master..spt_values (因为……

本文转载自微信公众号「SQL数据库」,作者丶平凡世界   。转载本文请联系开发公众号

spt_values是什么

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

  1. select * from master..spt_values 

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

结果为:

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

spt_values连续记录

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

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

结果为:

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

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

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

  1. SELECT  
  2.   SUBSTRING(CONVERT(CHAR(32), 
  3.   DATEADD(HH,number,CONCAT('2021-01-05',' ''00:00')),120),1,16) AS GroupDay 
  4. FROM 
  5.   master..spt_values  
  6. WHERE TYPE = 'P'  
  7. AND DATEDIFF(HH,DATEADD(HH,number, 
  8. CONCAT('2021-01-05',' ''00:00')), 
  9. CONCAT('2021-01-05',' ''23:00'))>=0 

(提示:可以左右滑动代码)结果为:

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

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

  1. SELECT  
  2.   CONVERT(NVARCHAR(10), DATEADD(DAY, number, '2021-01-01'),120) AS GroupDay 
  3. FROM  
  4.   master..spt_values  
  5. WHERE TYPE = 'P'  
  6.  AND number <= DATEDIFF(DAY'2021-01-01''2021-01-31'

结果为:

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

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

  1. SELECT  
  2.   SUBSTRING(CONVERT(NVARCHAR(10), DATEADD(MONTH, number, '2021-01-01'),120),1,7) AS GroupMonth 
  3. FROM  
  4.   master..spt_values  
  5. WHERE TYPE = 'P'   
  6. AND number <= DATEDIFF(MONTH'2021-01-01''2021-12-01'

结果为:

spt_values应用实例

有如下一张表Test

要求:显示1月份所有日期的DataValue值,如果没有值的,就显示为0。

分析:我们数据库中只存储了4条数据,这时候我们可以利用SQL的表spt_values来实现。

解法:

  1. SELECT DATEADD(DAY, number, CONVERT(DATETIME, '2021-01-01')) [DataTime], 
  2.        ISNULL(DataValue,0) DataValue 
  3. FROM master..spt_values 
  4.     LEFT JOIN Test 
  5.         ON DATEADD(DAY, number, CONVERT(DATETIME, '2021-01-01')) = [DataTime] 
  6. WHERE type = 'P' 
  7.       AND number 
  8.       BETWEEN 0 AND DATEDIFF(DAY'2021-01-01', DATEADD(MONTH, 1, '2021-01-01'))-1; 

结果为:

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

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


本文转载自网络,原文链接:https://mp.weixin.qq.com/s/UM-bhNshVGe7xL8fQob6Cw
本站部分内容转载于网络,版权归原作者所有,转载之目的在于传播更多优秀技术内容,如有侵权请联系QQ/微信:153890879删除,谢谢!

推荐图文


随机推荐