BigQuery SQL递归查询指定节点所有子节点报错解决方案

BigQuery 递归 CTE 存在明确语法限制：不允许在 WHERE 子句的嵌套表达式子查询（包括 IN、NOT IN 后的子查询）中直接引用递归CTE本身。你原写法中两处SELECT DISTINCT id FROM tbl_1都属于违规的嵌套子查询引用，因此触发400错误。
除此之外原写法还存在冗余逻辑：锚点阶段提前查询根节点的直接子节点属于多余操作，递归过程会自动完成层级遍历；额外写NOT IN去重的逻辑也可以通过更高效的方式实现。

BigQuery 递归查询所有子节点的标准实现是通过JOIN关联上一层递归结果，替代IN子查询写法，既符合语法要求，执行效率也更高：

WITH RECURSIVE tbl_1 AS (
  -- 锚点：仅查询指定的根问题节点
  SELECT id, parent
  FROM source_table
  WHERE id = 'xxxxxxxxxxx' -- 替换为实际传入的目标问题ID
  UNION ALL
  -- 递归段：关联上一层结果，查找所有子节点
  SELECT s.id, s.parent
  FROM source_table s
  INNER JOIN tbl_1 t 
    ON s.parent = t.id
)
SELECT * FROM tbl_1;

特殊场景适配（防循环引用） #

如果你的数据集可能存在循环关联（比如A的父节点是B，B的父节点是A），可以增加路径追踪字段避免递归死循环：

WITH RECURSIVE tbl_1 AS (
  SELECT 
    id, 
    parent,
    [id] AS traversed_path -- 数组存储已遍历的节点ID
  FROM source_table
  WHERE id = 'xxxxxxxxxxx'
  UNION ALL
  SELECT 
    s.id, 
    s.parent,
    t.traversed_path || [s.id]
  FROM source_table s
  INNER JOIN tbl_1 t 
    ON s.parent = t.id
    AND s.id NOT IN UNNEST(t.traversed_path) -- 跳过已遍历节点，切断循环
)
SELECT id, parent FROM tbl_1;

• 递归执行流程：第一轮获取根问题节点，第二轮匹配所有直接挂载在根问题下的一级回答，第三轮匹配所有挂载在一级回答下的二级回复，逐层向下遍历直到没有新的子节点，自动覆盖所有层级的关联内容
• 如果不需要返回根问题本身，仅需获取所有回答，在最终SELECT语句中增加过滤条件WHERE id != 'xxxxxxxxxxx'即可
• 该写法避免了嵌套子查询的性能损耗，数据量较大时执行速度明显优于原IN子查询写法

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