复杂SQL查询

先给大家献上一张图片，如下所示：

“红色”正方形代表文件夹，“绿色”正方形代表视频文件，右上角输入框可以实现文件夹或视频名称的模糊查询，模糊查询有如下要求：

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，大功告成！我真的太难了！