先给大家献上一张图片,如下所示:
“红色”正方形代表文件夹,“绿色”正方形代表视频文件,右上角输入框可以实现文件夹或视频名称的模糊查询,模糊查询有如下要求:
1.比如,当用户输入“资本”时,显而易见,有一个文件夹的名字和两个视频的名字都符合,则应该显示出符合查询条件的文件夹及文件夹下的所有视频文件,效果图如下所示:
2.当用户输入“全球”时,没有符合查询条件的文件夹,但有符合查询条件的视频文件,则应该显示出符合查询条件的视频文件和该文件所在的文件夹,效果图如下所示:
到这里,需求已经很清晰,下面来看看我们的数据表结构,如下所示:
SET FOREIGN_KEY_CHECKS=0;
DROP TABLE IF EXISTS `edu_video_manage_toc`;
CREATE TABLE `edu_video_manage_toc` (
`id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键',
`user_id` int(11) DEFAULT NULL COMMENT '用户id',
`parent_id` int(11) DEFAULT NULL COMMENT '父id',
`state` int(11) DEFAULT NULL COMMENT '状态(1:正常;2:暂时删除;3:永久删除)',
`type` varchar(255) DEFAULT NULL COMMENT '文件类型(adir:文件夹;mp3:视频)',
`video_name` varchar(255) DEFAULT NULL COMMENT '视频名称',
`course_name` varchar(255) DEFAULT NULL COMMENT '课程名称',
`teacher_name` varchar(255) DEFAULT NULL COMMENT '讲师名称',
`subject_id` int(11) DEFAULT NULL COMMENT '专业分类',
`video_cover_url` varchar(255) DEFAULT NULL COMMENT '视频封面',
`video_location` varchar(255) DEFAULT NULL COMMENT '视频地址',
`video_duration` varchar(255) DEFAULT NULL COMMENT '视频时长',
`video_size` varchar(255) DEFAULT NULL COMMENT '视频大小',
`check_status` int(255) DEFAULT NULL COMMENT '审核状态(1:成功;2:失败;3:待审核)',
`upload_time` datetime DEFAULT NULL COMMENT '上传时间',
`update_time` datetime DEFAULT NULL COMMENT '上传时间',
`delete_time` datetime DEFAULT NULL COMMENT '删除时间',
`remaining_day` int(11) DEFAULT NULL COMMENT '剩余天数',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=22 DEFAULT CHARSET=utf8;
当parent_id=0时为文件夹,当parent_id>0时为视频文件,当某一文件夹的id等于某一视频文件的parentId时,则代表他们存在父子关系,此时的视频文件存放在此时的文件夹下。
好了,需求和数据表结构已经非常清晰了,接下来我们着手写一下这个模糊查询的SQL,如下所示:
-- 按节名称查询
SELECT
*
FROM
edu_video_manage_toc
WHERE
id IN (
SELECT
m.parentId
FROM
(
SELECT
t.parentId parentId,
t.userId userId,
t.state state,
t.checkStatus checkStatus
FROM
(
SELECT
edu_video_manage_toc.parent_id parentId,
edu_video_manage_toc.user_id userId,
edu_video_manage_toc.state state,
edu_video_manage_toc.check_status checkStatus
FROM
edu_video_manage_toc
WHERE
edu_video_manage_toc.user_id = 23288
AND edu_video_manage_toc.state = 1
-- AND edu_video_manage_toc.video_name LIKE concat('%', '资本', '%')
) AS t
WHERE
t.checkStatus = 1
OR t.checkStatus IS NULL
) m
WHERE
m.parentId > 0
AND m.userId = 23288
AND m.state = 1
AND (
m.checkStatus = 1
OR m.checkStatus IS NULL
)
)
AND edu_video_manage_toc.user_id = 23288
AND edu_video_manage_toc.state = 1
AND (
edu_video_manage_toc.check_status = 1
OR edu_video_manage_toc.check_status IS NULL
)
UNION
-- 按章名称查询
SELECT
*
FROM
edu_video_manage_toc
WHERE
parent_id IN (
SELECT
m.id
FROM
(
SELECT
t.id id,
t.userId userId,
t.state state,
t.checkStatus checkStatus
FROM
(
SELECT
edu_video_manage_toc.id id,
edu_video_manage_toc.user_id userId,
edu_video_manage_toc.state state,
edu_video_manage_toc.check_status checkStatus
FROM
edu_video_manage_toc
WHERE
edu_video_manage_toc.user_id = 23288
AND edu_video_manage_toc.state = 1
-- AND edu_video_manage_toc.video_name LIKE concat('%', '资本', '%')
) AS t
WHERE
t.checkStatus = 1
OR t.checkStatus IS NULL
) m
WHERE
m.userId = 23288
AND m.state = 1
AND (
m.checkStatus = 1
OR m.checkStatus IS NULL
)
)
AND edu_video_manage_toc.user_id = 23288
AND edu_video_manage_toc.state = 1
AND (
edu_video_manage_toc.check_status = 1
OR edu_video_manage_toc.check_status IS NULL
)
UNION
-- 补集
SELECT
*
FROM
(
SELECT
*
FROM
edu_video_manage_toc
WHERE
edu_video_manage_toc.user_id = 23288
AND edu_video_manage_toc.state = 1
-- AND edu_video_manage_toc.video_name LIKE concat('%', '资本', '%')
) AS t
WHERE
t.check_status = 1
OR t.check_status IS NULL;
查询结果如下所示:
输入“资本”,查询结果如下所示:
输入“全球”,查询结果如下所示:
OK,大功告成!我真的太难了!