SQL题目9：计算流动人员的集合

with ts as
 (select 'a' as p_name, 1 as p_id
    from dual
  union all
  select 'b', 1
    from dual
  union all
  select 'c', 1
    from dual
  union all
  select 'a', 2
    from dual
  union all
  select 'd', 2
    from dual
  union all
  select 'e', 2
    from dual
  union all
  select 'b', 3
    from dual
  union all
  select 'f', 3
    from dual
  
  union all
  select 'k', 4
    from dual
  union all
  select 'i', 7
    from dual
  union all
  select 'i', 4
    from dual
  union all
  select 'j', 4
    from dual
  
  union all
  select 'a', 5
    from dual
  union all
  select 'p', 5
    from dual
  union all
  select 'q', 5
    from dual
  
  union all
  select 'z', 6 from dual
  
  ),
ts1 as --将有关系的人的名称进行一一对应 ,并规定小的为父  大的为子,形成单向树状结构
 (select distinct least(t1.p_name, t2.p_name) as fu,
                  greatest(t1.p_name, t2.p_name) as zi
    from ts t1
   inner join ts t2
      on t1.p_id = t2.p_id
     and t1.p_name <> t2.p_name),
ts2(zi,
max_fu) as --将所有顶级父节点按照树状结构进行单向搜索
 (select zi, fu as max_fu --将顶级父节点记录下来 方便后面分组
    from ts1
   where fu in (select fu --此条件可筛选出此方向的顶级父节点 但不包含单个节点
                  from ts1
                minus
                select zi from ts1)
  union all
  select t1.zi, t2.max_fu from ts1 t1 inner join ts2 t2 on t1.fu = t2.zi),
ts3 as --将所有与顶级父节点有关的所有子节点 和 父节点 本身 以及 单个节点 进行合并 
 (select max_fu, zi
    from ts2
  union
  select distinct p_name, p_name
    from ts
   where p_name not in (select zi from ts1)) --此条件可筛选出所有的顶级父节点和单个节点
select listagg(zi, ',') within group(order by zi) as flag ---将所有子节点 和父节点 按照顶级父节点分组 并且合并成一行
  from ts3
 group by max_fu
复制