一、需求
一条sql检索存在时间段内的记录
表:base
id | start_time | end_time |
---|---|---|
1 | 2021-01-01 12:01:12 | |
2 | 2021-01-01 14:01:12 | 2021-01-01 14:06:12 |
3 | 2021-01-01 15:01:12 | 2021-02-01 05:06:12 |
4 | 2021-02-01 15:01:12 | 2021-02-01 15:06:12 |
5 | 2021-02-01 16:01:12 | 2021-02-01 17:06:12 |
6 | 2021-02-01 19:01:12 | |
… | … | … |
二
效果(1)
检索时间段为
2021-02-01
日的数据
id | start_time | end_time |
---|---|---|
1 | 2021-01-01 12:01:12 | |
3 | 2021-01-01 15:01:12 | 2021-02-01 05:06:12 |
4 | 2021-02-01 15:01:12 | 2021-02-01 15:06:12 |
5 | 2021-02-01 16:01:12 | 2021-02-01 17:06:12 |
6 | 2021-02-01 19:01:12 |
实现SQL
SELECT * FROM base where start_time<'2021-02-02' and (end_time>'2021-02-01' or end_time is null)
/**
*sql语句纯手写重在参考实现逻辑
**/
效果(2)
检索时间段为
2021-02-01
日的数据(将时间段外的数据排除
)
id | start_time | end_time |
---|---|---|
1 | 2021-02-01 00:00:00 | 2021-02-02 00:00:00 |
3 | 2021-02-01 00:00:00 | 2021-02-01 05:06:12 |
4 | 2021-02-01 15:01:12 | 2021-02-01 15:06:12 |
5 | 2021-02-01 16:01:12 | 2021-02-01 17:06:12 |
6 | 2021-02-01 19:01:12 | 2021-02-02 00:00:00 |
实现SQL
SELECT id,
case when start_time<'2021-02-01' then '2021-02-01' else start_time end start_time,
case when end_time is null or end_time>'2021-02-02' then least('2021-02-02',now()) else end_time end end_time
FROM base where start_time<'2021-02-02' and (end_time>'2021-02-01' or end_time is null)
/**
*sql语句纯手写重在参考实现逻辑
**/
◆ ◆ ◆ 结语 ◆ ◆ ◆
有不足之处,或表述不清楚的地方,请多多包含,共勉~~