巧用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来解决。
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】凌霞软件回馈社区,博客园 & 1Panel & Halo 联合会员上线
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】博客园社区专享云产品让利特惠,阿里云新客6.5折上折
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步
· DeepSeek “源神”启动!「GitHub 热点速览」
· 微软正式发布.NET 10 Preview 1:开启下一代开发框架新篇章
· C# 集成 DeepSeek 模型实现 AI 私有化(本地部署与 API 调用教程)
· DeepSeek R1 简明指南:架构、训练、本地部署及硬件要求
· 2 本地部署DeepSeek模型构建本地知识库+联网搜索详细步骤
2017-04-03 实现WinForm窗体的美化(借助第三方控件)
2016-04-03 jQuery Ajax全解析
2016-04-03 属性和字段的区别和联系
2016-04-03 C#中用PadLeft、PadRight 补足位数