01-数据库SQL递归查询

运用场景：树查询、多树查询、树过滤

适用范围：数据库通用

with t_data(node, parent_node) as (
    values ('node-1', null),
           ('node-2', 'node-1'),
           ('node-3', 'node-2'),
           ('node-4', 'node-3'),
           ('node-a', null),
           ('node-b', 'node-a'),
           ('node-c', 'node-b'),
           ('node-d', 'node-c')
),
     t_tree(node, parent_node) as (
         select node, parent_node
         from t_data
         where parent_node is null
           and node = 'node-1'
         union all   --递归查询的固定结构
         select n.node, n.parent_node
         from t_data n,
              t_tree r
         where n.parent_node = r.node
     )
select node, parent_node
from t_tree;