时间段搜索 sql

·  阅读 18

背景

数据库中有一张视频分片表,长这样

image.png

CREATE TABLE `video` (
  `id` bigint(20) NOT NULL COMMENT '主键',
  `start_time` datetime DEFAULT NULL COMMENT '视频开始时间',
  `end_time` datetime DEFAULT NULL COMMENT '视频结束时间',
  `video_url` varchar(200) DEFAULT NULL COMMENT '视频地址',
  `video_size` varchar(200) DEFAULT NULL COMMENT '视频大小',
  `create_time` datetime(3) DEFAULT CURRENT_TIMESTAMP(3) COMMENT '创建时间',
  `update_time` datetime(3) DEFAULT NULL COMMENT '更新时间',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC COMMENT='视频信息表';
复制代码

每一条记录都是一个分片,包含了一段时间的视频

要求

现在要求,前端选择一段时间后,后端查询这一段时间内的视频

思路

dd.jpg

如上图,随着“用户选择的开始时间”不断的右移,"视频"这条数据,从符合条件,最后变成不符合条件。那么它是从什么时候开始不符合条件的呢?

从上图可以看到,是从数据的结束时间小于“用户选择的开始时间” 开始的!也就是說,要想符合条件,数据的结束时间大于等于“用户选择的开始时间”

同理,随着“用户选择的结束时间”不断的移,原来符合条件的数据,最后变成不符合条件。什么时候开始不符合条件的呢?是从数据的开始时间大于“用户选择的结束时间”开始的!也就是說,要想符合条件,数据的开始时间小于“用户选择的结束时间””

两条规律相加,就得到我们的筛选标准

  1. 数据的开始时间小于“用户选择的结束时间”
  2. 数据的结束时间大于“用户选择的开始时间”

sql

SELECT
    * 
FROM
    video 
WHERE
    start_time <= '用户选择的结束时间' 
    AND end_time >= '用户选择的开始时间'
复制代码

后记

还有一种特殊情况上面没考虑到,如下

image.png

但上面的算法依然满足这种特殊情况

分类:
后端
标签:
收藏成功!
已添加到「」, 点击更改