问题描述
我需要以特定方式获得一棵树的有序层次结构.有问题的表看起来有点像这样(所有 ID 字段都是唯一标识符，为了示例，我简化了数据):
EstimateItemID EstimateID ParentEstimateItemID ItemType-------------- ---------- -------------------- ------——1 NULL 产品2 A 1 产品3 一 2 服务4 NULL 产品5 A 4 产品6 一 5 服务7 A 1 服务8 A 4 产品树结构的图形视图(* 表示服务"):
<前>一种___/\___/1 4//2 7* 5 8//3* 6*使用这个查询，我可以获得层次结构(假设'A'是一个唯一标识符，我知道它不是在现实生活中):
DECLARE @EstimateID uniqueidentifier选择@EstimateID = 'A';温度为(SELECT * FROM EstimateItemWHERE EstimateID = @EstimateID联合所有SELECT ei.* FROM EstimateItem eiINNER JOIN temp x ON ei.ParentEstimateItemID = x.EstimateItemID)选择 * 从临时这给了我 EstimateID 'A' 的孩子，但按照它在表中出现的顺序.即:
估计物品ID--------------12345678不幸的是，我需要的是一个有序的层次结构，其结果集遵循以下约束:
<前>1.每个分支必须分组2. ItemType 'product' 和 parent 的记录是顶级节点3. ItemType 'product' 和非 NULL 父级的记录分组在顶级节点之后4. ItemType 'service' 的记录是分支的底部节点
所以，在这个例子中，我需要结果的顺序是:
估计物品ID--------------12374586
我需要在查询中添加什么来完成此操作?
试试这个:
;WITH 项目为 (SELECT EstimateItemID, ItemType, 0 AS 级别, CAST(EstimateItemID AS VARCHAR(255)) AS 路径从估计项目哪里 ParentEstimateItemID 为 NULL AND EstimateID = @EstimateID联合所有选择 i.EstimateItemID, i.ItemType, 等级 + 1, CAST(Path + '.' + CAST(i.EstimateItemID AS VARCHAR(255)) AS VARCHAR(255))FROM EstimateItem iINNER JOIN 项目 itms ON itms.EstimateItemID = i.ParentEstimateItemID)SELECT * FROM items ORDER BY PathWith Path - 按父节点排序的行
如果您想按每个级别的 ItemType 对子节点进行排序，则可以使用 Path 的 Level 和 SUBSTRING代码>列....
这里 SQLFiddle 带有数据示例
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 productGraphical view of the tree structure (* denotes 'service'):
A
___/ \___
/
1 4
/ /
2 7* 5 8
/ /
3* 6*
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
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
8Unfortunately, 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?
Try 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
If you want sort childnodes by ItemType for each level, than you can play with Level and SUBSTRING of Pathcolumn....
Here SQLFiddle with sample of data
这篇关于CTE递归获取树层次结构的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持编程学习网！