如何在BigQuery中递归查询指定记录的完整祖先链

在BigQuery中，用WITH RECURSIVE语法就能简洁实现这个需求，不需要复杂逻辑。假设你的表名为your_table，以下是针对id=145的查询语句：

WITH RECURSIVE ancestor_tree AS (
  -- 初始步骤：获取目标记录
  SELECT id, parentId, 1 AS level
  FROM your_table
  WHERE id = 145
  
  UNION ALL
  
  -- 递归步骤：向上查找父级记录，直到根节点（parentId=0）
  SELECT t.id, t.parentId, at.level + 1 AS level
  FROM your_table t
  JOIN ancestor_tree at ON t.id = at.parentId
  WHERE t.parentId != 0 -- 根节点的parentId为0，停止递归
)
-- 按level倒序，得到从根到目标记录的顺序
SELECT id, parentId
FROM ancestor_tree
ORDER BY level DESC;

说明： #

• 初始查询先定位到目标记录（id=145），并标记层级为1。
• 递归部分通过JOIN关联父级记录，层级递增，直到找到parentId=0的根节点后停止。
• 最后通过ORDER BY level DESC把结果从根节点到目标记录排序，和你期望的输出一致。

如果需要适配不同的目标id，只需要修改初始查询中的WHERE id = 145即可。

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