两个时间段是否有交集。
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 >= #{entryDate} AND IFNULL(t1.exit_date, curdate()) <= #{exitDate})
OR(t1.entry_date <= #{entryDate} AND IFNULL(t1.exit_date, curdate()) >= #{exitDate})
OR(t1.exit_date >= #{entryDate} AND IFNULL(t1.exit_date, curdate()) <= #{exitDate})
</if>
>= 表示 >=
<= 表示 <=