我有一个句点，我想排除日期范围
举个例子
id startDate endDate
316 2015-02-01 NULL
排除表
id ExclusionStartDate ExclusionEndDate
316 2015-02-15 2015-02-18
316 2015-03-10 2015-03-15
316 2015-04-01 2015-04-30
我搜索结果：
Id startDate endDate
316 2015-02-01 2015-02-14
316 2015-02-19 2015-03-09
316 2015-03-16 2015-03-31
316 2015-05-01 null
我可以使用递归查询吗？
尝试：
DECLARE @i TABLE(id int, sd DATE, ed DATE)
DECLARE @e TABLE(id int, sd DATE, ed DATE)
INSERT INTO @i VALUES
(316, '20150201', NULL),
(317, '20150202', NULL)
INSERT INTO @e VALUES
(316, '20150215', '20150218'),
(316, '20150310', '20150315'),
(316, '20150401', '20150430'),
(317, '20150405', '20150530')
;WITH cte AS
(
SELECT id, sd, ROW_NUMBER() OVER(PARTITION BY id ORDER BY ord, sd) AS rn FROM
(
SELECT id, sd, 0 AS ord FROM @i
UNION ALL
SELECT id, DATEADD(dd, -1, sd), 1 AS ord FROM @e
UNION ALL
SELECT id, DATEADD(dd, 1, ed), 1 AS ord FROM @e
UNION ALL
SELECT id, ed, 2 AS ord FROM @i
) t
)
SELECT c1.id, c1.sd, c2.sd AS ed FROM cte c1
JOIN cte c2 ON c1.rn + 1 = c2.rn AND c1.id = c2.id
WHERE c2.rn % 2 = 0
ORDER BY c2.id, c2.rn
输出：
id sd ed
316 2015-02-01 2015-02-14
316 2015-02-19 2015-03-09
316 2015-03-16 2015-03-31
316 2015-05-01 NULL
317 2015-02-02 2015-04-04
317 2015-05-31 NULL
本文收集自互联网，转载请注明来源。
如有侵权，请联系 [email protected] 删除。
我来说两句