開始時間(start_time)と終了時間(end_time)を持つテーブル(例: schedule)で、あるレコードXと時間が重複したデータを抽出するSQLは、
SELECT * FROM schedule WHERE start_time < レコードXのend_time AND end_time > レコードXのstart_time
です。 しかしstart_timeとend_timeの関係性を考えるのに毎回頭が混乱してしまうので、今回はこのSQLの導出方法を書いておきます。
ちなみに、このSQLは、
一方のend_timeが他方のstart_timeと同じときには重複してい ないと判定する場合です。
例えば、[start_time, end_time] = [14: 00, 15:00]と[start_time, end_time] = [15: 00, 16:00]の2つのレコードは重複として判定しません。
[start_time, end_time] = [14: 00, 15:00]と[start_time, end_time] = [15: 00, 16:00]を重複と判定する場合は、不等号にイコール記号を加えてください。
重なりのパターン
レコードXとほかのレコードとの重なりのパターンは以下のレコードABCDEFの6パターンです。
このうち、レコードXと重複するのは、レコードB,C,D,E、重複しないのがレコードA,Fです。
レコードB,C,D,Eに当てはまり、レコードA,Fに当てはまらない条件を導き出せればよいのですが、自分にはすぐには導き出せませんでした。
重ならない条件
そこで、一旦重ならないパターンに焦点を当てます。
重ならないパターンはレコードA,Fの2つで、レコードAは、
end_time <= レコードXのstart_time
となっているもの、
もう片方(レコードF)は、
レコードXのend_time <= start_time
です。(不等式にイコールを、含むかどうかは仕様によります。今回は、一方のend_timeが他方のstart_timeと同じときには重複してい ないと判定するものとします)
このどちらかの条件を満たしたすべてのレコードがレコードXと重複しません。式で書くと以下になります。
(end_time <= レコードXのsrart_time) OR (レコードXのend_time <= start_time)
重なる条件
重ならない条件式ができあがったので、これの否定をとれば、重なる条件式になります。
NOT((end_time <= レコードXのstart_time) OR (レコードXのend_time <= start_time))
ド・モルガンの法則により、
NOT(end_time <= レコードXのsrart_time) AND NOT(レコードXのend_time <= start_time) ↓ (end_time > レコードXのsrart_time) AND (レコードXのend_time > start_time)
となり、不等式が導出できました。
