"快速展开"层次结构表的方法是什么？

Question

"快速展开"层次结构表的方法是什么？

sql-servert-sqlsql-server-2012hierarchybigdata

3

我有一个非常庞大的层次结构表格，无法修改。表格中的节点具有Id、ParentId、Level和一些数据。 Level表示级别为N的节点不仅可以是级别为N-1的子级，还可以是级别为N-2、N-3等的子级。好消息是，级别数量有限 - 只有8个级别。级别1位于层次结构的顶部，级别8是其末端。

现在，我需要根据级别的位置对该表进行扁平化处理。结果应该像这样：

Lvl1   Lvl2   Lvl3   ...   Lvl8
xxx    xxx    null         xxx
xxx    null   xxx          xxx
xxx    null   null         xxx
xxx    xxx    xxx          xxx

第一步 由于级别数量有限，首先的想法是在ParentId = Id上多次使用LEFT JOIN将所有表连接起来。但这会导致级别改变位置，因为可能跳过了第6级，第5级取代了其位置。

第二步 因此，我使用了CASE WHEN根据行的级别选择值。

-- LEVEL 4
CASE
    WHEN lvl6.[Level] = 4 THEN lvl6.Data -- in case levels 6 and 5 were skipped, we can find 4th level data here
    WHEN lvl5.[Level] = 4 THEN lvl5.Data
    WHEN lvl4.[Level] = 4 THEN lvl4.Data
    ELSE NULL
END AS l4Data,

它解决了我的问题，但速度非常慢。

第三步 级别的组合也是有限的（1-2-3-4-5-6-7-8，1-3-5-6-7-8等）。因此，我决定使用更多的 LEFT JOIN 将所有级别的组合粘合在一起：

WITH
   l7 AS (SELECT * FROM myTable WHERE [Level] = 7),
   l6 AS (SELECT * FROM myTable WHERE [Level] = 6),
...
FROM l7
...
LEFT JOIN l6 AS l6_7 ON l7.ParentId = l6_7.Id       -- 7-6-5-4-1
LEFT JOIN l5 AS l5_7 ON l6_7.ParentId = l5_7.Id
LEFT JOIN l4 AS l4_7 ON l5_7.ParentId = l4_7.Id
LEFT JOIN l1 AS l1_7 ON l4_7.ParentId = l1_7.Id

然后我使用COALESCE选择数据：

COALESCE(l3.Data, l3_1.Data, l3_2.Data, l3_3.Data) AS l3Data,

这使得我的查询非常复杂和难以扩展，但目前为止这是我实现的最快结果。

有没有更快、更小的方法来压平这个表格？任何帮助将不胜感激。

提前致谢！

- Waldemar

通常使用CTE，使用哪个版本的SQL Server？ - Mike Miller

谢谢，如果下面的答案不清楚，请回复评论，我会发布一些内容。 - Mike Miller

我很感激您的帮助，因为我看不出Shnugo的答案如何处理跳过一个或多个级别的情况。 - Waldemar

一般来说，你可以拥有整洁的代码或快速的代码，但通常不能两者兼备。 - Nick.McDermaid

可能你需要在问题中澄清的是（请提供一个例子），第7级上的给定节点A可以有第2级上的直接父节点B，且没有定义中间级别。如果您提供这个作为示例数据，对于每个人来说都会更清晰（并确认这个假设）。 - Nick.McDermaid

1个回答

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Shnugo · Accepted Answer

以下是一个使用递归公共表达式的示例：

老实说：如果数据量非常大，我不认为这种方法会很快...

有一个HIERARCHYID 数据类型，但是您说您不能更改表的结构...

DECLARE @t TABLE(Name VARCHAR(100),id INT,parentId INT);

INSERT INTO @t VALUES
('Element 1',1,0)
,('Element 1.1',2,1)
,('Element 1.2',3,1)
,('Element 1.3',4,1)

,('Element 1.1.1',5,2)
,('Element 1.1.2',6,2)
,('Element 1.2.1',7,3)

,('Element 1.2.1.1',8,7)
,('Element 1.2.1.2',9,7);


WITH CTE AS
( 
       SELECT   * 
              ,CAST(parentId AS VARCHAR(MAX))  + ',' + CAST(CAST(id AS VARCHAR(MAX)) AS VARCHAR(MAX)) AS IdListTopDown
                ,CAST(Name AS varchar(MAX)) AS NameList
    FROM @t
    WHERE parentId = 0
    UNION ALL
    SELECT t.* 
             ,CAST(c.IdListTopDown AS VARCHAR(MAX)) + ',' + CAST(CAST(t.id AS VARCHAR(MAX)) AS VARCHAR(MAX))
               ,CAST(c.NameList + ' | ' + t.Name AS varchar(MAX))
       FROM @t AS t
       JOIN CTE c ON c.id = t.parentId
)
SELECT  CTE.*
FROM  CTE
WHERE NOT EXISTS(SELECT * FROM @t WHERE parentId=CTE.id)
ORDER BY CTE.IdListTopDown

结果

Element 1.1.1       5   2   0,1,2,5     Element 1 | Element 1.1 | Element 1.1.1
Element 1.1.2       6   2   0,1,2,6     Element 1 | Element 1.1 | Element 1.1.2
Element 1.2.1.1     8   7   0,1,3,7,8   Element 1 | Element 1.2 | Element 1.2.1 | Element 1.2.1.1
Element 1.2.1.2     9   7   0,1,3,7,9   Element 1 | Element 1.2 | Element 1.2.1 | Element 1.2.1.2
Element 1.3         4   1   0,1,4       Element 1 | Element 1.3