时间戳

首页 > 解决方案 > 使用 SQL 创建分层树

sql-server - 使用 SQL 创建分层树

问题描述

我有以下情况。我有一个包含公司组的表和一个包含分配给其他公司组的公司组的表。

包含所有公司组的表格：

CompanyGroupName    |   SomeOtherData
---                 |   ---
CompanyGroupA       |   ...
CompanyGroupB       |   ...
CompanyGroupC       |   ...
CompanyGroupD       |   ...
CompanyGroupE       |   ...
CompanyGroupF       |   ...
CompanyGroupG       |   ...
CompanyGroupH       |   ...
...                 |   ...

带有已分配公司组的表：

CompanyGroupParent  |   CompanyGroupChild
---                 |   ---
CompanyGroupA       |   CompanyGroupB
CompanyGroupD       |   CompanyGroupE
CompanyGroupF       |   CompanyGroupG
CompanyGroupB       |   CompanyGroupC
CompanyGroupE       |   CompanyGroupG
...                 |   ...

我想要的是一个具有以下结构的列表

TOP Level       |   Level 1         |   Level 2         |   Level N
CompanyGroupA   |   CompanyGroupB   |   CompanyGroupC   |   ...
CompanyGroupD   |   CompanyGroupE   |   CompanyGroupG
CompanyGroupF   |   CompanyGroupG
CompanyGroupH

我已经想到的是我可以/应该！？使用 CTE 和递归语句。

因此我需要识别锚元素。这相对容易，就好像公司组没有被列为子级一样，它自动成为根元素。used 语句返回结果集

TOP Level       |   Level 1         
CompanyGroupA   |   NULL
CompanyGroupD   |   NULL
CompanyGroupF   |   NULL
CompanyGroupH   |   NULL

我现在不明白的是如何构建UNION ALL递归语句的一部分以获得所需的输出。

关键是，树可能有多个根元素和未知的层级深度。

如果有人可以帮助我，那就太好了。

提前谢谢亚历克斯

标签： sql-serverrecursiontree

解决方案

对于完整的解决方案，单独的递归查询是不够的。必须将公司转至各自的级别列，并且由于结果列的数量是可变的，因此您将需要动态 SQL。

样本数据

create table CompanyGroups
(
  Child  nvarchar(7),
  Parent nvarchar(7)
);

insert into CompanyGroups (Child, Parent) values
('Comp100', null     ),
('Comp110', 'Comp100'),
('Comp111', 'Comp110'),
('Comp120', 'Comp100'),
('Comp200', null     ),
('Comp210', 'Comp200'),
('Comp211', 'Comp210'),
('Comp300', null     ),
('Comp400', null     ),
('Comp410', 'Comp400'),
('Comp420', 'Comp400');

解决方案

该位Level为每个公司生成。

with rcte as
(
  select cg.Child as Company,
         0 as Level
  from CompanyGroups cg
  where cg.Parent is null
    union all
  select cg.Child,
         r.Level + 1
  from rcte r
  join CompanyGroups cg
    on cg.Parent = r.Company
)
select r.Level, r.Company
from rcte r
order by r.Level, r.Company;

0这将是 3 个级别（ 、1、2）的先前信息的非动态枢轴。透视此信息还需要一个额外的RowNum列。

with rcte as
(
  select cg.Child as Company,
         0 as Level
  from CompanyGroups cg
  where cg.Parent is null
    union all
  select cg.Child,
         r.Level + 1
  from rcte r
  join CompanyGroups cg
    on cg.Parent = r.Company
),
data as
(
  select r.Level,
         row_number() over(partition by r.Level order by r.Company) as RowNum,
         r.Company
  from rcte r
)
select p.[0] as LVL0,
       p.[1] as LVL1,
       p.[2] as LVL2
from data d
pivot (max(d.Company) for d.Level in ([0], [1], [2])) p;

现在是最后一步，将不同的级别编号转换为子字符串，并将它们插入到处理N级别的动态 SQL 语句中。

-- get level list substrings
declare @selectList nvarchar(100);
declare @pivotList nvarchar(100);

with rcte as
(
  select cg.Child as Company,
         0 as Level
  from CompanyGroups cg
  where cg.Parent is null
    union all
  select cg.Child,
         r.Level + 1
  from rcte r
  join CompanyGroups cg
    on cg.Parent = r.Company
),
lvls as
(
  select r.Level
  from rcte r
  group by r.Level
)
select @selectList = string_agg('p.[' + convert(nvarchar(3), l.Level) + '] as LVL' + convert(nvarchar(3), l.Level), ', ') within group (order by l.Level),
       @pivotList = string_agg('[' + convert(nvarchar(3), l.Level) + ']', ', ') within group (order by l.Level)
from lvls l;

-- merge substrings in statement
declare @stmt nvarchar(2000) = 
'with rcte as
(
  select cg.Child as Company,
         0 as Level
  from CompanyGroups cg
  where cg.Parent is null
    union all
  select cg.Child,
         r.Level + 1
  from rcte r
  join CompanyGroups cg
    on cg.Parent = r.Company
),
data as
(
  select r.Level,
         row_number() over(partition by r.Level order by r.Company) as RowNum,
         r.Company
  from rcte r
)
select ' + @selectList + '
from data d
pivot (max(d.Company) for d.Level in (' + @pivotList + ')) p;'
    
-- execute statement
exec (@stmt);

结果

LVL0    LVL1    LVL2
------- ------- -------
Comp100 Comp110 Comp111
Comp200 Comp120 Comp211
Comp300 Comp210 null
Comp400 Comp410 null
null    Comp420 null

小提琴来看看一切在行动。

推荐阅读