CREATE TABLE `zhkt_course_chapter` (
`id` bigint NOT NULL COMMENT '唯一id',
`course_id` bigint NOT NULL COMMENT '所属课程id',
`name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '名称',
`parent_id` bigint NOT NULL DEFAULT '0' COMMENT '父级id,根级父级为0,默认0',
`status` int DEFAULT '0' COMMENT '状态:0正常,1禁用,默认0',
`sort` int DEFAULT NULL COMMENT '排序字段',
`deleted` tinyint DEFAULT '0' COMMENT '逻辑删除:0未删除,1删除,默认0',
`version` int DEFAULT NULL COMMENT '版本',
`create_by` bigint DEFAULT NULL COMMENT '创建者id',
`create_time` datetime DEFAULT NULL COMMENT '创建时间',
`update_by` bigint DEFAULT NULL COMMENT '更新者',
`update_time` datetime DEFAULT NULL COMMENT '更新时间',
PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci ROW_FORMAT=DYNAMIC COMMENT='智慧课堂-课程章节表';
- 查询符合条件数据,包含模糊查询(模糊查出子集,再追查父级,直到根级):
WITH RECURSIVE Ancestors AS (
-- 初始查询,找到指定课程和名称的子节点
SELECT
id,
course_id,
name,
parent_id,
status,
sort,
deleted,
version,
create_by,
create_time,
update_by,
update_time
FROM
zhkt_course_chapter
WHERE
deleted = 0 AND course_id = "1840215851826491393" AND name LIKE "%章节名称1.1.2%" -- 这里替换为你要查找的课程ID和名称
UNION ALL
-- 递归查询,找到父节点
SELECT
p.id,
p.course_id,
p.name,
p.parent_id,
p.status,
p.sort,
p.deleted,
p.version,
p.create_by,
p.create_time,
p.update_by,
p.update_time
FROM
zhkt_course_chapter p
INNER JOIN
Ancestors a ON p.id = a.parent_id
)
SELECT DISTINCT * FROM Ancestors;