环境: MYSQL 8.0 + windows 10
1、在TEST数据库中创建 表CTE_TEST.
CREATE TABLE `test`.`cte_test`( test_id INT, test_name VARCHAR(50), parent_test_id INT, created_by INT, creation_date TIMESTAMP ); 例子数据: INSERT INTO `test`.`cte_test`( test_id, test_name, parent_test_id ) VALUES (1,'A1', NULL), (2,'B1',NULL), (3,'C3',1), (4,'D4',3), (5,'E5',3), (6,'B7',2), (7,'C7',4); /** 层次结构 1 / 3 |\ 4 5 | 7 **/ SELECT * FROM `test`.`cte_test`; DELETE FROM `test`.`cte_test`; WITH recursive RECURSIVE_CTE(test_id, test_name, parent_test_id) AS( SELECT test_id, test_name, parent_test_id FROM `test`.`cte_test` cte1 WHERE cte1.test_id = 7 UNION ALL SELECT ct.test_id, ct.test_name, ct.parent_test_id FROM `test`.`cte_test` CT INNER JOIN RECURSIVE_CTE CTE ON CT.test_id = cte.parent_test_id ) SELECT * FROM RECURSIVE_CTE cte2 WHERE cte2.parent_test_id IS NULL -- 查最顶层的记录行 -- cte2.test_id = 4 ; SELECT * FROM `test`.`all_policies` ap WHERE ap.object_name LIKE '%RCV%' -- 'rcv_transactions'
SQL SERVER 2008 以上; ORACLE 11G以上, MYSQL 8.0 都可以使用 表表达式实现递归。
标签:递归,parent,cte,CTE,MYSQL,test,id,name From: https://www.cnblogs.com/samrv/p/18069318