我觉得还是看业务场景，比如说我这里有一个业务场景就是需要查询树状结点下面的所有子节点信息，这种情况下用 recursive cte 查询就很方便，一次就能查完，IO 都在数据库做了，不然的话还得多次查询数据库.
一个简单的示例
```
CREATE TABLE binary_tree (
id INT PRIMARY KEY,
value VARCHAR(255),
parent_id INT,
FOREIGN KEY (parent_id) REFERENCES binary_tree(id)
);
INSERT INTO binary_tree (id, value, parent_id) VALUES
(1, 'Root', NULL),
(2, 'Left Child', 1),
(3, 'Right Child', 1),
(4, 'Left Grandchild', 2),
(5, 'Right Grandchild', 2),
(6, 'Another Left Grandchild', 3),
(7, 'Another Right Grandchild', 3);
WITH RECURSIVE tree_cte AS (
-- Base case: select the root node
SELECT id, value, parent_id, 0 AS level, CAST(id AS text) AS path
FROM binary_tree
WHERE parent_id IS NULL
UNION ALL
-- Recursive case: select child nodes
SELECT c.id, c.value, c.parent_id, p.level + 1, CONCAT(p.path, ',', CAST(c.id AS text))
FROM binary_tree c
JOIN tree_cte p ON c.parent_id = p.id
)
SELECT id, value, parent_id, level, path
FROM tree_cte
ORDER BY path;
1,Root,,0,1
2,Left Child,1,1,"1,2"
4,Left Grandchild,2,2,"1,2,4"
5,Right Grandchild,2,2,"1,2,5"
3,Right Child,1,1,"1,3"
6,Another Left Grandchild,3,2,"1,3,6"
7,Another Right Grandchild,3,2,"1,3,7"
``` |
