代码之家 › 专栏 › 技术社区 › martin

在一个表中查找重叠的(日期/时间)行

mysql

martin · 技术社区 · 17 年前

会议中心
会议开始日期时间
会议结束日期时间

期望输出: 对于要输出的每对重叠行

在MySQL中执行这种查询最有效的方法是什么?

4 回复 | 直到 8 年前

Quassnoi 17 年前

SELECT  m1.meetingID, m1.meetingStart, m1.meetingEnd, m2.meetingID
FROM    t_meeting m1, t_meeting m2
WHERE   (m2.meetingStart BETWEEN m1.meetingStart AND m1.meetingEnd
        OR m2.meetingEnd BETWEEN m1.meetingStart AND m1.meetingEnd)
        AND m1.meetingID <> m2.meetingID

这将选择每对两次。

如果希望每对仅选择一次,请使用:

SELECT  m1.meetingID, m1.meetingStart, m1.meetingEnd, m2.meetingID
FROM    t_meeting m1, t_meeting m2
WHERE   (m2.meetingStart BETWEEN m1.meetingStart AND m1.meetingEnd
        OR m2.meetingEnd BETWEEN m1.meetingStart AND m1.meetingEnd)
        AND m2.meetingID > m1.meetingID

meetingStart 和 meetingEnd 以使查询高效地工作。

MySQL INDEX MERGE 运行此查询,在当前实现中效率不高。

您也可以尝试使用:

SELECT  m1.*, m2.*
FROM    (
        SELECT  m1.meetingID AS mid1, m2.meetingID AS mid2
        FROM    t_meeting m1, t_meeting m2
        WHERE   m2.meetingStart BETWEEN m1.meetingStart AND m1.meetingEnd
                AND m2.meetingID <> m1.meetingID
        UNION
        SELECT  m1.meetingID, m2.meetingID
        FROM    t_meeting m1, t_meeting m2
        WHERE   m2.meetingEnd BETWEEN m1.meetingStart AND m1.meetingEnd
                AND m2.meetingID <> m1.meetingID
        ) mo, t_meeting m1, t_meeting m2
WHERE   m1.meetingID = mid1
        AND m2.meetingID = mid2

Señor Reginold Francis 10 年前

尝试使用此查询。Quassnoi的解决方案修改为忽略一个预订的结束与另一个预订的开始相同的情况。

SELECT  m1.meetingID_id, m1.meetingStart , m1.meetingEnd, m2.meetingID_id
FROM    bookings m1, bookings m2
WHERE   (m2.meetingStart BETWEEN m1.start AND DATE_SUB(m1.meetingEnd, INTERVAL 1 second)
        OR DATE_SUB(m2.meetingEnd, INTERVAL 1 second) BETWEEN m1.meetingStart AND m1.end)
        AND m1.meetingID_id > m2.meetingID_id

Simon Lehmann 17 年前

可能是这样的:

SELECT m1.meetingID, m2.meetingID
FROM meeting AS m1, meeting AS m2
WHERE m1.meetingID < m2.meetingID
    AND m1.meetingStart BETWEEN m2.meetingStart AND m2.meetingEnd
    OR m1.meetingEnd BETWEEN m2.meetingStart AND m2.meetingEnd

只选择 m1.meetingID < m2.meetingID

Jukka Dahlbom 17 年前

将会议的开始和结束时间添加到结果行:

SELECT m1.meetingID AS firstID, m1.meetingStart AS firstStart, 
m1.meetingEnd AS firstEnd, m2.meetingID AS secondID, 
m2.meetingStart AS secondStart, m2.meetingEnd AS secondEnd 
FROM meeting AS m1, meeting AS m2 
WHERE (m2.meetingStart BETWEEN m1.meetingStart AND m1.meetingEnd) 
AND (m1.meetingID != m2.meetingID)

这样,m2将始终是同时启动或在m1之后启动的,

您不需要对照会议结束进行检查,因为重叠仅通过比较会议开始就可以可靠地检测到。