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

问题描述

我有一个表格，该表格在每行中存储有开始日期/时间和结束日期/时间的会议.

I have a table which stores in each row a meeting with start date/time and end date/time.

meetingID int
MeetingStart datetime
MeetingEnd datetime

meetingID int
meetingStart datetime
meetingEnd datetime

所需的输出:对于每对重叠的行，我想输出
MeetingID，meetingStart，meetingID，meetingEnd

Desired output:For each pair of overlapping rows I would like to output
meetingID, meetingStart, meetingID, meetingEnd

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

What's the most efficient way to perform such a query in MySQL?

推荐答案

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

这将选择每对两次.

如果您希望每对仅被选择一次，请使用:

If you want each pair to be selected just once, use:

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上都有索引，以使查询有效地工作.

Make sure you have indexes on meetingStart and meetingEnd for the query to work efficiently.

MySQL可能会使用INDEX MERGE来运行此查询，这在当前实现中不是很有效.

MySQL, however, will probably use INDEX MERGE to run this query, which is not very efficient in current implementation.

您也可以尝试使用:

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

，它更复杂，但很有可能会运行得更快.

, which is more complex but will most probably run a little bit faster.

这篇关于在一张表中查找重叠的(日期/时间)行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！