怎么用Spt

181次阅读

共计 1821 个字符，预计需要花费 5 分钟才能阅读完成。

本篇内容主要讲解“怎么用 Spt_Values 解决 SQL 中的连续日期问题”，感兴趣的朋友不妨来看看。本文介绍的方法操作简单快捷，实用性强。下面就让丸趣 TV 小编来带大家学习“怎么用 Spt_Values 解决 SQL 中的连续日期问题”吧!

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 解决 SQL 中的连续日期问题”有了更深的了解，不妨来实际操作一番吧！这里是丸趣 TV 网站，更多相关内容可以进入相关频道进行查询，关注我们，继续学习！

正文完