如何产生连续时间?(案例)
案例 如何 时间 连续 产生
2023-09-27 14:21:29 时间
原问题如下:
Insus.NET的问题解决,分2步,1,创建一个辅助表,2,合并数据。
程式码搜寻出来如图:
IF OBJECT_ID(N'TEMPDB.DBO.#search_data') IS NOT NULL DROP TABLE #search_data GO CREATE TABLE #search_data ([times] DATETIME,[val] INT) INSERT INTO #search_data ([times],[val]) VALUES ('2001-01-01 00:00:00',13), ('2001-01-01 00:10:00',22), ('2001-01-01 00:20:00',31), ('2001-01-01 00:40:00',5 ), ('2001-01-01 00:50:00',64), ('2001-01-01 02:30:00',73), ('2001-01-01 04:10:00',63), ('2001-01-01 04:50:00',12), ('2001-01-01 06:30:00',32)
接下来,我们可以创建一张辅助表:
DECLARE @start_datetime DATETIME = '2000-01-01 00:00:00', @end_datetime DATETIME = '2000-02-01 00:00:00' ;WITH DTs([times]) AS ( SELECT @start_datetime UNION ALL SELECT [times] = DATEADD(MINUTE,10, [times]) FROM [DTs] WHERE [times] < @end_datetime ) SELECT [times] FROM [DTs] OPTION (MaxRecursion 0)
总共产生4465行记录。
更多相关辅助表,可以参考《使用CTE生成辅助表(数字或时间)等》https://www.cnblogs.com/insus/p/10946112.html 和《快速生成基数的辅助表》https://www.cnblogs.com/insus/p/10946112.html
为了更好操作,把辅助表的产生数据存入一张临时表中:
IF OBJECT_ID(N'TEMPDB.DBO.#base') IS NOT NULL DROP TABLE #base GO CREATE TABLE #base ([times] DATETIME) DECLARE @start_datetime DATETIME = '2000-01-01 00:00:00', @end_datetime DATETIME = '2000-02-01 00:00:00' ;WITH DTs([times]) AS ( SELECT @start_datetime UNION ALL SELECT [times] = DATEADD(MINUTE,10, [times]) FROM [DTs] WHERE [times] < @end_datetime ) INSERT INTO #base ([times]) SELECT [times] FROM [DTs] OPTION (MaxRecursion 0)
第2步,合并数据,把程序搜索结果与辅助表的数据进行合并merge:
MERGE #search_data AS Target USING (SELECT [times] FROM #base) AS Source ON (Target.[times] = Source.[times]) WHEN NOT MATCHED BY TARGET THEN INSERT ([times],[val]) VALUES ([times],0); SELECT [times],[val] FROM #search_data
OK,实现方法仅供参考,完整代码:
IF OBJECT_ID(N'TEMPDB.DBO.#search_data') IS NOT NULL DROP TABLE #search_data GO CREATE TABLE #search_data ([times] DATETIME,[val] INT) INSERT INTO #search_data ([times],[val]) VALUES ('2001-01-01 00:00:00',13), ('2001-01-01 00:10:00',22), ('2001-01-01 00:20:00',31), ('2001-01-01 00:40:00',5 ), ('2001-01-01 00:50:00',64), ('2001-01-01 02:30:00',73), ('2001-01-01 04:10:00',63), ('2001-01-01 04:50:00',12), ('2001-01-01 06:30:00',32) IF OBJECT_ID(N'TEMPDB.DBO.#base') IS NOT NULL DROP TABLE #base GO CREATE TABLE #base ([times] DATETIME) DECLARE @start_datetime DATETIME = '2000-01-01 00:00:00', @end_datetime DATETIME = '2000-02-01 00:00:00' ;WITH DTs([times]) AS ( SELECT @start_datetime UNION ALL SELECT [times] = DATEADD(MINUTE,10, [times]) FROM [DTs] WHERE [times] < @end_datetime ) INSERT INTO #base ([times]) SELECT [times] FROM [DTs] OPTION (MaxRecursion 0) MERGE #search_data AS Target USING (SELECT [times] FROM #base) AS Source ON (Target.[times] = Source.[times]) WHEN NOT MATCHED BY TARGET THEN INSERT ([times],[val]) VALUES ([times],0); SELECT [times],[val] FROM #search_data
相关文章
- 如何设计一个自动化测试框架?一个案例教会你
- 微服务架构案例(06):通过业务、应用、技术、存储方面,聊聊架构
- 【软件测试面试题】大厂头条:如何定位bug?实际案例拿offer还不简单......
- 【综合案例】原生JS实现购物商城
- Google Earth Engine(GEE)——GEE的WHITTAKER平滑算法案例分析
- 【MATLAB教程案例4】直接序列扩频通信系统的MATLAB仿真
- 【MATLAB教程案例1】通信系统中成形滤波器原理的MATLAB设计实现
- IDEA下操作Git分支案例
- 小白也能看懂的悲观锁,乐观锁(附生动案例)
- 数据挖掘案例分享:利用BP神经网络算法进行用户行为分析(二)
- 【快应用】如何配置快应用图标,及几种常见的无效配置案例
- 《像计算机科学家一样思考Python》——第4章 案例研究:接口设计 4.1 乌龟世界
- 微信小程序canvas2d使用封装与案例使用
- Vue学习第33天——路由守卫(导航守卫)超详解讲解及使用场景、案例练习
- 【java养成】:案例(打印三角形,超市购物、随机点名)