CTE递归以获取树层次结构 [英] CTE Recursion to get tree hierarchy
问题描述
我需要以特定的方式获取树的有序层次结构。有问题的表看起来像这样(所有ID字段都是唯一标识符,为便于示例,我简化了数据):
I need to get an ordered hierarchy of a tree, in a specific way. The table in question looks a bit like this (all ID fields are uniqueidentifiers, I've simplified the data for sake of example):
EstimateItemID EstimateID ParentEstimateItemID ItemType
-------------- ---------- -------------------- --------
1 A NULL product
2 A 1 product
3 A 2 service
4 A NULL product
5 A 4 product
6 A 5 service
7 A 1 service
8 A 4 product
树形结构的图形视图(*表示服务 ):
Graphical view of the tree structure (* denotes 'service'):
A
___/ \___
/ \
1 4
/ \ / \
2 7* 5 8
/ /
3* 6*
使用此查询,我可以得到层次结构(只是假装'A'是一个唯一标识符,我知道它不是现实生活中的):
Using this query, I can get the hierarchy (just pretend 'A' is a uniqueidentifier, I know it isn't in real life):
DECLARE @EstimateID uniqueidentifier
SELECT @EstimateID = 'A'
;WITH temp as(
SELECT * FROM EstimateItem
WHERE EstimateID = @EstimateID
UNION ALL
SELECT ei.* FROM EstimateItem ei
INNER JOIN temp x ON ei.ParentEstimateItemID = x.EstimateItemID
)
SELECT * FROM temp
这为我提供了EstimateID'A'的子级,但是按照它在表格中出现的顺序。即:
This gives me the children of EstimateID 'A', but in the order that it appears in the table. ie:
EstimateItemID
--------------
1
2
3
4
5
6
7
8
不幸的是,什么我需要的是一个具有以下约束的结果集的有序层次结构:
Unfortunately, what I need is an ordered hierarchy with a result set that follows the following constraints:
1. each branch must be grouped
2. records with ItemType 'product' and parent are the top node
3. records with ItemType 'product' and non-NULL parent grouped after top node
4. records with ItemType 'service' are bottom node of a branch
因此,在此示例中,我需要结果的顺序为:
So, the order that I need the results, in this example, is:
EstimateItemID
--------------
1
2
3
7
4
5
8
6
我需要添加什么查询才能完成
What do I need to add to my query to accomplish this?
推荐答案
尝试一下:
;WITH items AS (
SELECT EstimateItemID, ItemType
, 0 AS Level
, CAST(EstimateItemID AS VARCHAR(255)) AS Path
FROM EstimateItem
WHERE ParentEstimateItemID IS NULL AND EstimateID = @EstimateID
UNION ALL
SELECT i.EstimateItemID, i.ItemType
, Level + 1
, CAST(Path + '.' + CAST(i.EstimateItemID AS VARCHAR(255)) AS VARCHAR(255))
FROM EstimateItem i
INNER JOIN items itms ON itms.EstimateItemID = i.ParentEstimateItemID
)
SELECT * FROM items ORDER BY Path
使用路径
-按父节点排序的行
With Path
- rows a sorted by parents nodes
如果您想按每个级别按 ItemType
对子节点进行排序,则可以与 Level
和 Path
列的 SUBSTRING
一起玩...
If you want sort childnodes by ItemType
for each level, than you can play with Level
and SUBSTRING
of Path
column....
此处 SQLFiddle 带有数据示例
这篇关于CTE递归以获取树层次结构的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!