SQL Server 中如何做到连续时间段的拆分?
程序员文章站
2022-03-26 17:56:48
今天在工作中遇到了一个很实际的问题,客户在OA接口的员工休假中间表中提供了连续时间段的休假记录,例如: 张三,2018-12-1 ~2018-12-31 ,病假,31天。这样带来的问题是,如果我需要统计张三从12月1号到12月15号的休假天数,单从这一整条连续记录是无法统计的。这时候就需要我们将一条 ......
今天在工作中遇到了一个很实际的问题,客户在OA接口的员工休假中间表中提供了连续时间段的休假记录,例如: 张三,2018-12-1 ~2018-12-31 ,病假,31天。这样带来的问题是,如果我需要统计张三从12月1号到12月15号的休假天数,单从这一整条连续记录是无法统计的。这时候就需要我们将一条长记录进行拆分。
这里记录下我自己的思路:
1:利用系统表得到0-2047的序列 ,2048*2048绝对够用了吧
SELECT sv.number AS n FROM MASTER.dbo.spt_values AS sv WHERE sv.[type]='P'
结果如图:
2:利用CROSS APPLY 得到最终序列(从1开始,最后一行是2048*2048)
WITH t1 AS (SELECT sv.number AS n FROM MASTER.dbo.spt_values AS sv WHERE sv.[type]='P'), t2 AS ( SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 1)) AS rid FROM t1 AS a CROSS APPLY t1 AS b) ----------------- SELECT *FROM t2
结果如图
你千万别告诉我400多万都不够你拆的啊,小心我揍你
3:奉上完整代码
DECLARE @begindate DATE =CAST('2017-12-1' AS date) DECLARE @enddate DATE =CAST('2017-12-31' AS date) BEGIN WITH t1 AS (SELECT sv.number AS n FROM MASTER.dbo.spt_values AS sv WHERE sv.[type]='P'), t2 AS ( SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 1)) AS rid FROM t1 AS a CROSS APPLY t1 AS b) ----------------- --SELECT *FROM t2 SELECT DATEADD(DAY,t2.rid-1,@begindate) as date FROM t2 WHERE t2.rid BETWEEN -1 AND (DATEDIFF(DAY,@begindate,@enddate)+1) END
PS:因为序列从1开始,用Dateadd函数我需要从0天开始加起(作为第一天),因此注意下上述代码的-1 和+1的作用。
效果如图:
最后:
关于With 关键字、row_nunber()over()开窗函数 以及cross apply请您自行脑补学习,以免一知半解
推荐阅读
-
基于SQL Server中如何比较两个表的各组数据 图解说明
-
如何在SQL Server 2014中用资源调控器压制你的存储?
-
基于SQL Server中如何比较两个表的各组数据 图解说明
-
如何控制SQL Server中的跟踪标记
-
SQL Server 2008 R2——查找最小nIndex,nIndex存在而nIndex+1不存在 求最小连续数组中的最大值
-
如何在SQL Server 2014中用资源调控器压制你的存储?
-
SQL Server如何查找表名或列名中包含空格的表和列
-
sql server把退款总金额拆分到尽量少的多个订单中详解
-
在sql中如何把一列的值拆分成多列
-
如何快速删掉SQL Server登录时登录名下拉列表框中的选项