mysql表重叠_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

这将选择每对两次.

如果您希望每对选择一次,请使用：

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

,这更复杂,但最有可能运行一点点.