SQL两个时间段是否存在交叉

两个时间段是否有交集。

SELECT * FROM test_table
WHERE
    (start_time >= a AND start_time <= b)
    OR (start_time <= a AND end_time >= b)
    OR (end_time >= a AND end_time <= b)

使用场景：
数据库表中有 进入时间：entry_date ，退出时间：exit_date
前端传入 开始时间 - 结束时间
判断：开始时间 - 结束时间 与entry_date - exit_date是否有交叉
退出时间exit_date可能为null，即：到目前为止还未退出

SELECT
    t1.project_id as projectId,
    t1.group_id as groupId,
    t2.group_name as groupName,
    t1.bid_id as bidId,
    t1.post as post,
    t1.entry_date as entryDate,
    t1.exit_date as exitDate,
    t1.payment_method as paymentMethod,
    t1.payment_price as paymentPrice,
    t1.contract_ids as contractIds,
    t1.contract_start as contractStart,
    t1.pay_bank as payBank,
    t1.pay_bank_branch as payBankBranch,
    t1.pay_card_number as payCardNumber
    FROM
    worker_post_detail t1
    LEFT JOIN worker_class_group t2
    ON t1.group_id=t2.id
    WHERE t1.approval_status='1'
		AND(t1.entry_date >= '2021-12-01' AND IFNULL(t1.exit_date,  curdate()) <= '2021-12-29')
		OR(t1.entry_date <= '2021-12-01' AND IFNULL(t1.exit_date,  curdate()) >= '2021-12-29')
		OR(t1.exit_date >= '2021-12-01' AND IFNULL(t1.exit_date,  curdate()) <= '2021-12-29')

MyBtis中>=,<=不能直接用

<if test="entryDate != null and exitDate != null">
         AND(t1.entry_date &gt;= #{entryDate} AND IFNULL(t1.exit_date,  curdate()) &lt;= #{exitDate})
         OR(t1.entry_date &lt;= #{entryDate} AND IFNULL(t1.exit_date,  curdate()) &gt;= #{exitDate})
         OR(t1.exit_date &gt;= #{entryDate} AND IFNULL(t1.exit_date,  curdate()) &lt;= #{exitDate})
     </if>

>=   表示  >=
&lt;=   表示  <=

参考该帖子