WITH recursive 表名 AS ( 初始语句（非递归部分） UNION ALL 递归部分语句 ) [ SELECT| INSERT | UPDATE | DELETE]
数据准备
-- ---------------------------- -- Table structure for tree -- ---------------------------- DROP TABLE IF EXISTS `tree`; CREATE TABLE `tree` ( `id` int NOT NULL, `p_id` int DEFAULT NULL, `name` varchar(255) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; -- ---------------------------- -- Records of tree -- ---------------------------- BEGIN; INSERT INTO `tree` VALUES (1, NULL, 'A'); INSERT INTO `tree` VALUES (2, NULL, 'B'); INSERT INTO `tree` VALUES (3, 1, 'A3'); INSERT INTO `tree` VALUES (4, 1, 'A4'); INSERT INTO `tree` VALUES (5, 2, 'B5'); INSERT INTO `tree` VALUES (6, 2, 'B6'); INSERT INTO `tree` VALUES (7, 2, 'B7'); INSERT INTO `tree` VALUES (8, 3, 'A3-8'); INSERT INTO `tree` VALUES (9, 3, 'A3-9'); INSERT INTO `tree` VALUES (10, 3, 'A3-10'); INSERT INTO `tree` VALUES (11, 4, 'A4-11'); INSERT INTO `tree` VALUES (12, 4, 'A4-12'); INSERT INTO `tree` VALUES (13, 6, 'B6-13'); COMMIT; SET FOREIGN_KEY_CHECKS = 1;
自顶向下查询子树
with RECURSIVE full_tree (id, p_id, name) AS (select id, p_id, name from tree where p_id is null -- 查询条件 union all select t.id, t.p_id, t.name from tree t inner join full_tree on full_tree.id = t.p_id) select * from full_tree;
查询结果：
自底向上查找所有节点
with RECURSIVE filter_tree (id, p_id, name) AS (select id, p_id, name from tree where name like 'B%' -- 过滤条件 union all select t.id, t.p_id, t.name from tree t inner join filter_tree on filter_tree.p_id = t.id) select distinct * from filter_tree;
查询结果：
根据子节点id向上查找
with RECURSIVE filter_tree (id, p_id, name) AS (select id, p_id, name from tree where id=13 -- 过滤条件 union all select t.id, t.p_id, t.name from tree t inner join filter_tree on filter_tree.p_id = t.id) select distinct * from filter_tree;
查询结果：
