快速“扁平化"的方法层次表? [英] Fast way to "flatten" hierarchy table?
问题描述
我有一个非常庞大的表，它的层次结构无法修改.表中的节点具有Id，ParentId，Level和一些数据. Level意味着级别为N的节点不仅可以是级别N-1的子级，还可以是级别N-2，N-3的子级.好消息是级别的数量有限-有其中只有8个.级别1在层次结构的顶部，级别8在层次结构的末尾.
I've got a very huge table with hierarchy which can not be modified. Nodes in the table have an Id, ParentId, a Level and some data. The Level means that node with level N can be a child not only for level N-1 but also for level N-2, N-3 etc. The good news are that the number of levels is limited - there are only 8 of them. Level 1 is on the top of the hierarchy and level 8 is the end of it.
现在我需要根据级别的位置来平整该表.结果应该是这样的:
And now I need to flatten that table with respect to the place of the levels. The result should be like this:
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级取代了它.
First step
As the level number is limited, the first idea was to LEFT JOIN several times all the table on ParentId = Id. But this made levels change their place, as level 6 could be skipped and level 5 took it's place.
第二步所以我用CASE WHEN根据行的级别选择值.
Second step So I've used CASE WHEN to select value depending on the row's level.
-- 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,
它解决了我的问题，但是速度很慢.
It solved my problem but it was VERY slow.
第三步级别的组合也受到限制(1-2-3-4-5-6-7-8，1-3-5-6-7-8等)，因此我决定使用更多的LEFT JOIN将所有级别的组合粘合在一起:
Third step The combination of levels is also limited (1-2-3-4-5-6-7-8, 1-3-5-6-7-8, etc.) So I decided to use more LEFT JOINs to glue all combinations of levels together:
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,
这使我的查询非常复杂且难以扩展，但就目前而言，这是我实现的最快结果.
It made my query VERY complicated and hard to extend, but as for now it's the fastest result I've achieved.
有没有更快，更微小的方法来摆弄那张桌子?任何帮助将不胜感激.
Are there any faster and tiny ways to flattern that table? Any help will be appreciated.
提前谢谢！
推荐答案
这是一个如何使用递归CTE的示例:
This is an example how you could go with a recursive CTE:
说实话:使用巨大数据，我不希望这很快.
To be honest: I'd not expect this to be very fast with huge data...
存在 HIERARCHYID数据类型，但您说过，不允许您更改表的结构...
There is the HIERARCHYID data type, but you said, that you are not allowed to change the table's structure...
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
这篇关于快速“扁平化"的方法层次表?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
