一、数据结构
表数据关联方式为id和parent_id字段的方式
二、根据多个子节点反查上级所有的父级节点

WITH RECURSIVE parent_nodes as (
  SELECT id, parent_id, task_name,1 as level,id as child_id
  FROM 数据表
  WHERE id IN(子节点ID1, 子节点ID2, 子节点ID3)

  UNION ALL
  SELECT t.id, t.parent_id, t.task_name,p.level+1 as level,p.child_id
  FROM 数据表 t
  JOIN parent_nodes p on t.id = p.parent_id
)
SELECT DISTINCT * FROM parent_nodes;

三、查找里面的所有叶子节点

select * from 数据表 where id not in (
  select parent_id from 数据表
)

四、递归根据条件查询树形的父子节点

WITH RECURSIVE 
-- 向下查询所有子节点
down_tree AS (
    SELECT id, parent_id, name, 0 AS is_parent
    FROM tree_table
    WHERE id = 起始节点ID  -- 或使用其他条件
    
    UNION ALL
    
    SELECT t.id, t.parent_id, t.name, 0
    FROM tree_table t
    JOIN down_tree dt ON t.parent_id = dt.id
),

-- 向上查询所有父节点
up_tree AS (
    SELECT id, parent_id, name, 1 AS is_parent
    FROM tree_table
    WHERE id = 起始节点ID  -- 必须与上面相同
    
    UNION ALL
    
    SELECT t.id, t.parent_id, t.name, 1
    FROM tree_table t
    JOIN up_tree ut ON t.id = ut.parent_id
    WHERE t.id != ut.id  -- 避免自引用
)

-- 合并结果并去重
SELECT DISTINCT * FROM (
    SELECT * FROM down_tree
    UNION ALL
    SELECT * FROM up_tree
) combined_tree;