查找sql中的时隙

Question

我的表在下面

CREATE TABLE APPOINTMENT(
ID INT,
A_START DATETIME,
A_END DATETIME,
CLINIC_ID INT
)

我们插入了以下数据

INSERT INSERT INTO APPOINTMENT VALUES(1,'2021-07-21 01:00:00','2021-07-21 02:00:00',1)
INSERT INTO APPOINTMENT VALUES(2,'2021-07-21 05:00:00','2021-07-21 06:00:00',1)
INSERT INTO APPOINTMENT VALUES(3,'2021-07-21 05:30:00','2021-07-21 06:30:00',1)
INSERT INTO APPOINTMENT VALUES(4,'2021-07-21 08:00:00','2021-07-21 09:00:00',1)
INSERT INTO APPOINTMENT VALUES(5,'2021-07-21 08:40:00','2021-07-21 09:40:00',1)
INSERT INTO APPOINTMENT VALUES(6,'2021-07-21 11:00:00','2021-07-21 12:00:00',1)

预期结果

Slat start time      slat end time
2021-07-21 01:00:00 2021-07-21 02:00:00
2021-07-21 05:00:00 2021-07-21 06:30:00
2021-07-21 08:00:00 2021-07-21 09:40:00
2021-07-21 11:00:00 2021-07-21 12:00:00

因为在这些记录2和3中，4和5是相交的

我已经尝试了，但是无法找到预期的结果。

SELECT * FROM APPOINTMENT 
A,APPOINTMENT B
WHERE A.ID<>B.ID
AND (A.A_START<B.A_START AND A.A_START>B.A_END

http://sqlfiddle.com/!18/97b79/1

Answer 1

比较间隔以检查它们是否重叠的正确方法如下:

a.Start < b.End AND a.End > b.Start

如果您希望包含两个间隔彼此相连的情况，则将<更改为<=，将>更改为>=

SELECT *
FROM APPOINTMENT A
JOIN APPOINTMENT B ON A.ID <> B.ID
AND (A.A_START < B.A_END AND A.A_END > B.A_START)

---

然而，这似乎完全不是你真正想要的。你实际上只是在尝试合并重叠的间隔。

这种类型的问题被称为缺口和岛屿，有许多不同的解决方案。

这个技术相当直接:

• 使用LAG
标识每个组的起始位置
• 使用正在运行的COUNT为每个岛屿创建一个分组ID
• 按分组ID分组
• 如果你想为每一列做这个，比如CLINIC_ID，然后你在每个OVER中添加PARTITION BY子句

WITH Prev AS (
SELECT *,
PrevEnd = LAG(A_END) OVER (ORDER BY A_START)
FROM APPOINTMENT A
),
Groupings AS (
SELECT *,
GroupId = COUNT(CASE WHEN PrevEnd >= A_START THEN NULL ELSE 1 END)
OVER (ORDER BY A_START ROWS UNBOUNDED PRECEDING)
FROM Prev
)
SELECT
StartTime = MIN(A_START),
ENdTime = MAX(A_END)
FROM Groupings
GROUP BY GroupId
ORDER BY StartTime;

,db&lt的在小提琴