MYSQL: 表表示式（CTE）實現遞迴例項

環境： 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 都可以使用 表表示式實現遞迴。