我需要以特定的方式获取树的有序层次结构。有问题的表看起来像这样(所有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 Pathcolumn....
此处 SQLFiddle 带有数据示例
更多推荐
CTE递归以获取树层次结构
发布评论