如何在SQL Server中构建父子层次结构(从根目录开始)

Question

我正在尝试使用SQL server获取从根节点到其子节点的路径。

源数据看起来像：

源数据

目标应该看起来像：

目标数据

由于我将在专门使用ETL转换的ETL工具中实现这一点，我希望在不使用CONNECT BY等效方法的情况下实现此输出。下面的查询给了我结果和更多的记录：

select case when level02.geography_02 is not NULL
then '3'
else case when level01.geography_02 is not null
then '2'
else case when root.geography_02 is not null
then '1'
end 
end
end as levels,
root.geography_01 as root, root.geography_02 as super_parent,
case when level01.geography_02 is not null
then level01.geography_02
else ''
end as parent,
case when level02.geography_02 is not null
then level02.geography_02
else ''
end as child
from geo_table root
left join geo_table level01
on root.geography_02 = level01.geography_01
left join geo_table level02
on level01.geography_02 = level02.geography_01

你能谈谈如何获得所需的输出吗？

Answer 1

我认为您只需要一些过滤。也就是说，查询的其余部分也可以简化一点——特别是通过使用COALESCE():

select (case when level02.geoghraphy_02 is not NULL then '3'
when level01.geoghraphy_02 is not null then '2'
when root.geoghraphy_02 is not null then '1'
end) as levels,
root.geoghraphy_01 as root,
root.geoghraphy_02 as super_parent,
coalesce(level01.geography_02, '') as parent,
coalesce(level02.geography_02, '') as child
from geo_table root left join
geo_table level01
on root.geography_02 = level01.geography_01 left join
geo_table level02
on level01.geography_02 = level02.geography_01
where not exists (select 1
from geo_table gt
where gt.geography_02 = root.geography_01
);

基本上，您只需要将"根"限制为实际的根记录。你实际上已经处理了逻辑中更棘手的部分(在我看来(。