TA貢獻1963條經驗 獲得超6個贊
|-------------------| compare to this one |---------| contained within |----------| contained within, equal start |-----------| contained within, equal end |-------------------| contained within, equal start+end |------------| not fully contained, overlaps start |---------------| not fully contained, overlaps end |-------------------------| overlaps start, bigger |-----------------------| overlaps end, bigger |------------------------------| overlaps entire period
|-------------------| compare to this one |---| ends before |---| starts after
starts after end ends before start
|-------------| |-------| equal end with start of comparison period |-----| equal start with end of comparison period
SELECT *FROM periodsWHERE NOT (range_start > @check_period_end OR range_end < @check_period_start)
SELECT *FROM periodsWHERE range_start <= @check_period_end AND range_end >= @check_period_start
TA貢獻1946條經驗 獲得超4個贊
where ('1983-06-06' <= end) and ('1983-06-18' >= start)
TA貢獻1886條經驗 獲得超2個贊
DELIMITER ;;CREATE FUNCTION overlap_interval(x INT,y INT,a INT,b INT)RETURNS INTEGER DETERMINISTICBEGINDECLARE overlap_amount INTEGER; IF (((x <= a) AND (a < y)) OR ((x < b) AND (b <= y)) OR (a < x AND y < b)) THEN IF (x < a) THEN IF (y < b) THEN SET overlap_amount = y - a; ELSE SET overlap_amount = b - a; END IF; ELSE IF (y < b) THEN SET overlap_amount = y - x; ELSE SET overlap_amount = b - x; END IF; END IF; ELSE SET overlap_amount = 0; END IF; RETURN overlap_amount;END ;;DELIMITER ;
舉報