如何在递归SQL查询中查找子树中的所有节点？

WITH RECURSIVE cte (node, parent, depth) AS (
  -- 基本情况：查询根节点及其直接子节点
  SELECT node, parent, 1
  FROM tree
  WHERE parent IS NULL

  UNION ALL

  -- 递归情况：查询子节点及其子节点
  SELECT t.node, t.parent, cte.depth + 1
  FROM tree t
  JOIN cte ON t.parent = cte.node
)
SELECT node, parent, depth
FROM cte
WHERE node = '目标节点';