檢查MySQL中日期范圍的重疊該表用于存儲會話（事件）：CREATE TABLE session ( id int(11) NOT NULL AUTO_INCREMENT, start_date date, end_date date);INSERT INTO session (start_date, end_date)VALUES ("2010-01-01", "2010-01-10"), ("2010-01-20", "2010-01-30"), ("2010-02-01", "2010-02-15");我們不希望范圍之間發生沖突。假設我們需要在2010-01-05至2010-01-25之間插入一個新會話。我們想知道沖突的會話。這是我的查詢：SELECT *FROM sessionWHERE "2010-01-05" BETWEEN start_date AND end_date OR "2010-01-25" BETWEEN start_date AND end_date OR "2010-01-05" >= start_date AND "2010-01-25" <= end_date;結果如下：+----+------------+------------+| id | start_date | end_date |+----+------------+------------+| 1 | 2010-01-01 | 2010-01-10 || 2 | 2010-01-20 | 2010-01-30 |+----+------------+------------+有沒有更好的方法來實現這一目標？
3 回答
ibeautiful
TA貢獻1993條經驗 獲得超6個贊
SELECT * FROM tbl WHERE
existing_start BETWEEN $newStart AND $newEnd OR
existing_end BETWEEN $newStart AND $newEnd OR
$newStart BETWEEN existing_start AND existing_end
if (!empty($result))
throw new Exception('We have overlapping')
這3行sql子句涵蓋了4個需要重疊的情況。
一只萌萌小番薯
TA貢獻1795條經驗 獲得超7個贊
你可以多一點優化它。
SELECT * FROM tbl WHEREexisting_start BETWEEN $newSTart AND $newEnd OR$newStart BETWEEN existing_start AND existing_end
這將捕獲范圍重疊的所有四種情況，并排除它們不重復的兩種情況。
添加回答
舉報
0/150
提交
取消