SQL：具有n级的分层结构
我有一个分层结构存储在一个关系数据库中，这个关系数据库用树视图来表示。 每个节点的属性都有不同的字段，并通过ID知道它的父节点。 这是一个亲子关系模型。
如果一个节点有一个子节点，它在节点名称前面用[+]表示。 点击[+]可以展开节点并查看节点的子节点。 如果孩子有孩子节点到最底层，孩子本身就有[+]。
一个简化的示例树视图如下所示：
[+] A Land
[+] A.1 Car
A.1.A Motor
A.1.B Wheels
[+] B Sea
B.1 Sailing ship
[+] B.2 Motorboat
B.2.A Motor
[+] C Air
[+] C.1 Plane
C.1.A Turbine
C.2.B Wheels
可以在各种节点属性上设置一个或多个过滤器，例如显示名为“Motor”的后代的所有节点。 树视图看起来像：
[+] A Land
[+] A.1 Car
A.1.A Motor
[+] B Sea
[+] B.2 Motorboat
B.2.A Motor
由于我的数量有限，节点数量有限，所以这种结构可以满足我的需求（平庸的性能）。
现在我们要将树视图扩展到n级深度。
有嵌套集合模型，只要你不过滤掉东西，性能就很好。 这是因为，据我们所知，嵌套集不支持过滤。 我们还尝试了路径模型（SQL-Servers hierarchyid-datatype），但是如果您有很多级别，则过滤速度很慢。
我们的路径模型方法：假设您在tbale PMTable中的每个级别都有20个级别，每个级别都有很多节点，它具有hierarchyid-datatype类型的列路径。 然后，你不想查询（初始化TreeView）所有具有至少一个后代的顶级节点（必须不是直接后代，descandant可以有每个可能的级别），它们适用于过滤器（例如：名称LIKE'％motor％'AND type = 3，其中名称和类型是同一路径模型表中的列）。 我们还存储了节点的从零开始的级别以简化查询。
该查询可以是：
SELECT id, name
FROM PMTable WHERE level = 0
AND Path IN
(
SELECT Path WHERE Path.GetAncestor(Path.GetLevel() - 1)
FROM PMTable
WHERE name LIKE '%motor%' AND type = 3
)
ORDER BY name
此查询可能是平庸的性能，但正如您所看到的，在顶级查询中也有昂贵的子查询，该子查询必须从表中查询符合条件的所有节点。
但是用户点击小的[+]展开一个顶级节点，你必须查询所有二级节点，它将点击节点作为祖先，并且也匹配那个过滤条件（包含匹配的任何级别的descandants）。 如果一个节点本身符合该过滤条件（属于类型3并且名称中包含“电机”），则必须显示其所有后代。
在我们的例子中，这些查询的性能很差。
有没有其他的模型可以选择，或者有更好的性能。
谢谢！
自SQL Server 2005以来，T-SQL开发人员能够使用CTE结构对层次结构数据执行递归查询
如果您查看引用的SQL教程，您可以在上一屏截图中找到示例数据和示例CTE查询和层次结构级别
在SQL Server中，递归CTE形成如下
WITH cte AS (
{Anchor_Query}
UNION ALL
{Recursive part joining to cte}
)
SELECT * FROM cte
通过在CTE中的锚选择语句中将初始级别的层级添加为1，并在递归部分中将此初始级别增加1，您最终将得到最终数据集中的所有层次结构级别
请检查示例SQL教程
我一直在使用范围键为我的层次超过20年。 我们拥有大量和众多的替代层次结构，用于报告，处理和/或选择标准。 我还创建了一个用于快速导航和实用程序的函数库。
以下是一个快速示例。 请记住，我手动创建了范围键，它们通常是以编程方式创建/更新的。 另外，我通常有一个表示序列号来控制归因过程中的实际顺序。
真正的美妙之处在于，您可以在不使用递归查询的情况下轻松聚合可变深度数据。
下面的查询缺少我所有的帮手，因为我想说明这种技术。
Declare @OH table (OH_R1 int,OH_R2 int,OH_Lvl int,OH_Nr int,OH_Pt int,OH_Title varchar(100))
Insert into @OH Select 0,12,1,9,0,'Total'
Insert into @OH Select 1,4,2,100,9,'Land'
Insert into @OH Select 2,4,3,200,100,'Car'
Insert into @OH Select 3,3,4,300,200,'Motor'
Insert into @OH Select 4,4,4,400,200,'Wheels'
Insert into @OH Select 5,8,2,500,9,'Sea'
Insert into @OH Select 6,6,3,600,500,'Sailing Ship'
Insert into @OH Select 7,8,3,625,500,'Motor Boat'
Insert into @OH Select 8,8,4,650,625,'Motor'
Insert into @OH Select 9,12,2,800,9,'Air'
Insert into @OH Select 10,12,3,825,800,'Plane'
Insert into @OH Select 11,11,4,550,825,'Turbine'
Insert into @OH Select 12,12,4,550,825,'Wheele'
-- Show Nested/Filtered Hierarchy
Select A.*
,Nested=Replicate(' ',OH_Lvl-1)+OH_Title
,Hits=sum(hits)
From @OH A
Join (Select OH_R1,Hits=1 from @OH where OH_Title like '%motor%' and OH_Lvl=4) B on (B.OH_R1 between A.OH_R1 and A.OH_R2)
Group by A.OH_R1,A.OH_R2,A.OH_Lvl,A.OH_Nr,A.OH_Pt,A.OH_Title
Order by OH_R1
-- Show Actual Hierarchy
Select * from @OH Order by OH_R1
返回
OH_R1 OH_R2 OH_Lvl OH_Nr OH_Pt OH_Title Nested Hits
0 12 1 9 0 Total Total 2
1 4 2 100 9 Land Land 1
2 4 3 200 100 Car Car 1
3 3 4 300 200 Motor Motor 1
5 8 2 500 9 Sea Sea 1
7 8 3 625 500 Motor Boat Motor Boat 1
8 8 4 650 625 Motor Motor 1
使用嵌套集模型，找到所有具有名为“Motor”的后代的树应该非常简单：
SELECT
P.name -- Or whatever other columns you need
FROM
My_Tree D
INNER JOIN My_Tree P ON P.lft <= D.lft AND P.rgt >= D.rgt
WHERE
D.name = 'Motor'
如果您还想包含该节点的后代（即任何成员具有该名称的完整树），则可以轻松添加一个OR语句来抓取子节点。
上一篇: SQL: Hierarchical structure with n level
下一篇: SQL Server 2012 CTE Find Root or Top Parent of Hierarchical Data