檢查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");我們不希望范圍之間發(fā)生沖突。假設(shè)我們需要在2010-01-05至2010-01-25之間插入一個(gè)新會話。我們想知道沖突的會話。這是我的查詢：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;結(jié)果如下：+----+------------+------------+| id | start_date | end_date |+----+------------+------------+| 1 | 2010-01-01 | 2010-01-10 || 2 | 2010-01-20 | 2010-01-30 |+----+------------+------------+有沒有更好的方法來實(shí)現(xiàn)這一目標(biāo)？
3 回答
ibeautiful
TA貢獻(xiàn)1993條經(jīng)驗(yàn) 獲得超6個(gè)贊
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個(gè)需要重疊的情況。
一只萌萌小番薯
TA貢獻(xiàn)1795條經(jīng)驗(yàn) 獲得超7個(gè)贊
你可以多一點(diǎn)優(yōu)化它。
SELECT * FROM tbl WHEREexisting_start BETWEEN $newSTart AND $newEnd OR$newStart BETWEEN existing_start AND existing_end
這將捕獲范圍重疊的所有四種情況，并排除它們不重復(fù)的兩種情況。
添加回答
舉報(bào)
0/150
提交
取消