正确计算2个日期范围之间的时间

Question

我正在处理Datetime列，由于时间重叠，我在计算Time时遇到了问题。

这是我的样本数据

Declare @T table (ID Int, InTime Datetime,OutTime Datetime)
Insert into @T values (1,'2020-08-23 09:26:07.000','2020-08-23 09:57:55.000')
Insert into @T values (1,'2020-08-23 14:09:08.000','2020-08-26 08:13:45.000')
Insert into @T values (1,'2020-08-24 11:14:37.000','2020-08-24 18:07:25.000')
Insert into @T values (1,'2020-08-25 09:19:38.000','2020-08-25 19:19:29.000')
Insert into @T values (1,'2020-08-26 08:13:50.000','2020-08-28 08:39:23.000')
Insert into @T values (1,'2020-08-27 08:42:16.000','2020-08-27 11:38:06.000')
Insert into @T values (1,'2020-08-27 09:51:14.000','2020-08-28 18:23:06.000')
Insert into @T values (1,'2020-08-29 09:51:14.000','2020-08-30 18:23:06.000')

我的预期输出：

+----+-------------------------+--------------------------+
| ID |         InTime          |         OutTime          |
+----+-------------------------+--------------------------+
|  1 | 2020-08-23 09:26:07.000 | 2020-08-23 09:57:55.000  |
|  1 | 2020-08-23 14:09:08.000 | 2020-08-26 08:13:45.000  |
|  1 | 2020-08-26 08:13:50.000 | 2020-08-28 08:39:23.000  |
|  1 | 2020-08-29 09:51:14.000 | 2020-08-30 18:23:06.000  |
+----+-------------------------+--------------------------+

如果你看到表格中的第二个条目，我的Intime是2020-08-23 14:09:08.000，outTime是2020-08-16 08:13:45.000。因此，如果表中有任何介于23和26之间的条目，我们应该跳过该条目。所以我们需要跳过第24个和第25个条目。

有人能帮我查询一下吗。如有任何帮助，将不胜感激

我尝试了这个链接，但无法理解链接的逻辑

Answer 1

这是"聚集间隔"；。Snodgrass给出了一个经典的查询：

WITH  
T0 AS  
(SELECT PRE.id,
PRE.intime AS D1, PRE.outtime AS F1,  
DER.intime AS D2, DER.outtime AS F2  
FROM   @T PRE  
INNER JOIN @T DER  
ON PRE.intime <= DER.outtime
AND PRE.id = DER.id)  
SELECT DISTINCT id, D1 AS intime, F2 AS outtime
FROM   T0 AS I  
WHERE  NOT EXISTS (SELECT *  
FROM   @T SI1  
WHERE  (SI1.intime < I.D1  
AND I.D1 <= SI1.outtime
AND I.id = SI1.id )  
OR  (SI1.intime <= I.F2  
AND I.F2 < SI1.outtime
AND I.id = SI1.id))  
AND NOT EXISTS (SELECT *  
FROM   @T SI2
WHERE  D1 < SI2.intime  
AND  SI2.intime <= F2
AND  I.id = SI2.id  
AND  NOT EXISTS (SELECT *  
FROM   @T SI3  
WHERE  SI3.intime < SI2.intime  
AND  SI2.intime <= SI3.outtime
AND  SI2.id = SI3.id ));

Chris Date给出另一个版本：

WITH T  
AS (SELECT F.intime, L.outtime, F.id
FROM   @T AS F  
JOIN @T AS L  
ON F.outtime <= L.outtime
AND F.id = L.id
INNER JOIN @T AS E      
ON F.id = E.id  
GROUP  BY F.intime, L.outtime,  F.id  
HAVING COUNT(CASE  
WHEN (E.intime < F.intime AND F.intime <= E.outtime)  
OR (E.intime <= L.outtime AND L.outtime < E.outtime)
THEN 1  
END) = 0)  
SELECT id, intime, MIN(outtime) AS outtime  
FROM   T  
GROUP  BY id, intime;

最后我写了一篇：

WITH  
T0 AS -- suprime les périodes incluses
(SELECT DISTINCT Tout.id, Tout.intime, Tout.outtime
FROM   @T  AS Tout  
WHERE  NOT EXISTS(SELECT *  
FROM   @T  AS Tin  
WHERE  Tout.intime >= Tin.intime  
AND  Tout.outtime < Tin.outtime
AND Tout.id = Tin.id)),  
T1 AS -- ancres : périodes de tête...  
(SELECT Ta.*, 1 AS ITERATION  
FROM   T0 AS Ta  
WHERE  NOT EXISTS(SELECT *  
FROM   T0 AS Tb  
WHERE  Tb.outtime >= Ta.intime  
AND Tb.outtime  < Ta.outtime
AND Tb.id = Ta.id)  
UNION  ALL -- itération sur période dont le debut est inclus dans les bornes de la période ancre  
SELECT T1.id, T1.intime, T0.outtime, T1.ITERATION + 1
FROM   T1  
INNER JOIN T0  
ON T1.intime < T0.intime  
AND T1.outtime >= T0.intime  
AND T1.outtime < T0.outtime
AND T1.id = T0.id),  
T2 AS  
(SELECT *, ROW_NUMBER() OVER(PARTITION BY id, intime ORDER BY DATEDIFF(s, intime, outtime) DESC) AS N1,
ROW_NUMBER() OVER(PARTITION BY id, outtime ORDER BY DATEDIFF(s, intime, outtime) DESC) AS N2
FROM   T1)  
SELECT id, intime, outtime
FROM   T2  
WHERE  N1 = 1 AND N2 = 1;

哪个是递归查询

Itzik Ben Gan做了一些更复杂、更高性能的查询。。。