Select * from Table_XXX
(
//检索时段在起止时间内
StartTime >= #{StartDay} AND EndTime < #{EndDay}
//开始时间在检索时段内,结束时间在检索时段外
OR (StartTime >= #{StartDay} AND StartTime< #{EndDay} AND EndTime>#{EndDay})
//检索时段在起止时间外
OR (StartTime < #{StartDay} AND #{EndDay} <=EndTime)
//开始时间在检索时段外,结束时间在检索时段内
OR (StartTime < #{StartDay} AND EndTime>=#{StartDay} AND EndTime<#{EndDay})
);
合并检索条件同类项后为:
Select * from Table_XXX
(
StartTime >= #{StartDay} AND
(EndTime < #{EndDay} OR (StartTime< #{EndDay} AND EndTime>#{EndDay})))
OR (StartTime < #{StartDay} AND
(#{EndDay} <=EndTime) OR (EndTime>=#{StartDay} AND EndTime<#{EndDay})))
);