如何用递归方式替代多自连接实现SQL祖先节点查询？

问题描述 #

我有一张存储父子对数据的表，子节点可作为其他子节点的父节点。表中namekey代表子节点，namekeyow（namekey所有者）代表父节点。目前通过多次自连接的方式，从某个子节点出发查询它的6代祖先，SQL如下：

SELECT a1.namekey   n1,
       a1.namekeyow n2,
       a2.namekeyow n3,
       a3.namekeyow n4,
       a4.namekeyow n5,
       a5.namekeyow n6,
       a6.namekeyow n7
FROM   iacira a1
       LEFT JOIN iacira a2
              ON a2.namekey = a1.namekeyow
       LEFT JOIN iacira a3
              ON a3.namekey = a2.namekeyow
       LEFT JOIN iacira a4
              ON a4.namekey = a3.namekeyow
       LEFT JOIN iacira a5
              ON a5.namekey = a4.namekeyow
       LEFT JOIN iacira a6
              ON a6.namekey = a5.namekeyow

请问是否可以不使用多次自连接，而采用递归方式实现该查询？

回答 #

完全可以用递归方式实现，这种写法比多次自连接简洁得多，后续调整查询代数也更灵活。下面分数据库类型给出具体实现：

支持递归CTE的数据库（MySQL 8.0+、PostgreSQL、SQL Server等） #

递归CTE分为锚点成员和递归成员两部分：锚点定义查询的起始节点，递归成员逐层向上遍历祖先，直到达到指定代数或无更上层节点为止。

WITH RECURSIVE ancestor_tree AS (
    -- 锚点：替换为你要查询的目标子节点值
    SELECT 
        namekey AS n1,
        namekeyow AS ancestor_node,
        1 AS generation
    FROM iacira
    WHERE namekey = '目标子节点值'
    UNION ALL
    -- 递归：向上查询每一代祖先
    SELECT 
        at.n1,
        i.namekeyow,
        at.generation + 1
    FROM ancestor_tree at
    JOIN iacira i ON i.namekey = at.ancestor_node
    WHERE at.generation < 6 -- 限制最多查询6代祖先，对应原查询的n7
)
-- 将行数据转换为原查询的列格式
SELECT 
    n1,
    MAX(CASE WHEN generation = 1 THEN ancestor_node END) AS n2,
    MAX(CASE WHEN generation = 2 THEN ancestor_node END) AS n3,
    MAX(CASE WHEN generation = 3 THEN ancestor_node END) AS n4,
    MAX(CASE WHEN generation = 4 THEN ancestor_node END) AS n5,
    MAX(CASE WHEN generation = 5 THEN ancestor_node END) AS n6,
    MAX(CASE WHEN generation = 6 THEN ancestor_node END) AS n7
FROM ancestor_tree
GROUP BY n1;

Oracle数据库 #

Oracle使用CONNECT BY语法实现递归查询，写法如下：

SELECT 
    CONNECT_BY_ROOT namekey AS n1,
    MAX(CASE WHEN LEVEL = 1 THEN namekeyow END) AS n2,
    MAX(CASE WHEN LEVEL = 2 THEN namekeyow END) AS n3,
    MAX(CASE WHEN LEVEL = 3 THEN namekeyow END) AS n4,
    MAX(CASE WHEN LEVEL = 4 THEN namekeyow END) AS n5,
    MAX(CASE WHEN LEVEL = 5 THEN namekeyow END) AS n6,
    MAX(CASE WHEN LEVEL = 6 THEN namekeyow END) AS n7
FROM iacira
START WITH namekey = '目标子节点值'
CONNECT BY PRIOR namekeyow = namekey
AND LEVEL <= 6
GROUP BY CONNECT_BY_ROOT namekey;

说明 #

• 递归方式无需手动添加多层自连接，要查询更多代只需要修改generation < X或LEVEL <= X中的数值即可
• 如果不需要固定列格式，直接查询递归CTE或CONNECT BY的结果，能得到每一代的父子关系行数据，更便于后续处理

内容的提问来源于stack exchange，提问作者Theju112