sql - 展平sql分层数据

Question

是否可以在不知道根的情况下展平如下所示的分层数据？

我正在考虑使用下面的方法首先找到最终的父节点，然后使用递归层次结构 cte 来查找子节点

select parent,COUNT(*) from test_le where parent!=child group by parent

但我又想知道是否可以直接做

输入

parent,child,parent_catg,child_catg
A   A   FIN FIN
A   B   FIN FIN
B   B   FIN FIN
A   C   FIN FIN
B   C   FIN FIN
C   C   FIN FIN
B   D   FIN FIN
C   D   FIN FIN
E   E   OTH OTH
E   F   OTH OTH
A   G   FIN FIN
B   G   FIN FIN
A   H   FIN FIN
P   P   FIN FIN
P   Q   FIN FIN

期望的输出

ultimate_parent child_nodes
A   B,C,D,G,H
E   F
P   Q

提前致谢

标签： sqlsql-serversql-server-2016hierarchical-data

Answer 1

在 SQL Server 中，使用 CTE（通用表表达式）几乎就是“如何直接访问它”。我加载了您的数据并尝试处理一个（下面的测试代码），但很快就遇到了您的数据的一些问题......

• 具有“自我识别”行（A，A）（B，B）等。让事情变得更加复杂
• 因为没有所有这些（没有（D，D）

也许更相关（我不确定技术术语），但这不是传统的层次结构。D 是 B 和 C 的孩子；B 是 A 的子节点，而 C 是 B 和 A 的子节点。因此，“常规”遍历层次结构的方法将不适用。

下面是我的编码尝试作为解决问题的开始，但鉴于所提供数据的性质，它不会起作用。

--CREATE TABLE #MyTable
-- (
--   Parent       char(1)  not null
--  ,Child        char(1)  not null
--  ,Parent_catg  char(3)  not null
--  ,Dhild_catg   char(3)  not null
-- )

--INSERT #MyTable values
--  ('A', 'A', 'FIN', 'FIN')
-- ,('A', 'B', 'FIN', 'FIN')
-- ,('B', 'B', 'FIN', 'FIN')
-- ,('A', 'C', 'FIN', 'FIN')
-- ,('B', 'C', 'FIN', 'FIN')
-- ,('C', 'C', 'FIN', 'FIN')
-- ,('B', 'D', 'FIN', 'FIN')
-- ,('C', 'D', 'FIN', 'FIN')
-- ,('E', 'E', 'OTH', 'OTH')
-- ,('E', 'F', 'OTH', 'OTH')
-- ,('A', 'G', 'FIN', 'FIN')
-- ,('B', 'G', 'FIN', 'FIN')
-- ,('A', 'H', 'FIN', 'FIN')
-- ,('P', 'P', 'FIN', 'FIN')
-- ,('P', 'Q', 'FIN', 'FIN')

;WITH cte
 as (

    select
       Parent
      ,Child
     from #MyTable
     where Child <> Parent
      and  Parent not in(select Child from #MyTable where Parent <> Child)

    union all select
       mt.Parent
      ,mt.Child
    from cte
     inner join #MyTable  mt
      on mt.Child = cte.Parent
    where mt.Child <> mt.Parent
     and cte.Child <> cte.Parent
    )

--select *
-- from cte
-- where Child <> Parent
-- order by Parent, Child

select
   Parent
  ,string_agg(Child, ',')
 from cte
 where Child <> Parent
 group by
   Parent