检测重叠事件
问题描述:
我有一个事件的表,用户可以输入:检测重叠事件
event_id | title | start | end
-----------------------------------------------------------------
0 | title 1 | 2014-10-29 09:00:00 | 2014-10-29 09:30:00
1 | title 2 | 2014-10-29 09:15:00 | 2014-10-29 09:45:00
2 | title 3 | 2014-10-29 10:00:00 | 2014-10-29 10:30:00
2 | title 3 | 2014-10-29 11:00:00 | 2014-10-29 11:30:00
我想如果有事件重叠在Cursor
返回的附加列。
event_id | title | start | end | conflict
---------------------------------------------------------------------------
0 | title 1 | 2014-10-29 09:00:00 | 2014-10-29 09:30:00 | 1
1 | title 2 | 2014-10-29 09:15:00 | 2014-10-29 09:45:00 | 1
2 | title 3 | 2014-10-29 10:00:00 | 2014-10-29 10:30:00 | 0
2 | title 3 | 2014-10-29 11:00:00 | 2014-10-29 11:30:00 | 0
我试图找到最好的方法来做到这一点。
答
有冲突,如果存在重叠当前事件的任何其它事件:
SELECT *,
EXISTS (SELECT 1
FROM events AS other
WHERE other.event_id != events.event_id
AND other.end > events.start
AND other.start < events.end) AS conflict
FROM events
谢谢!这正是我需要的 – chis54 2014-10-31 22:18:48