CTE递归获取树层次结构 [英] CTE Recursion to get tree hierarchy

问题描述

我需要以特定方式获得一棵树的有序层次结构.有问题的表看起来有点像这样(所有 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 Path

With 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               product

Graphical 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
      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?

解决方案

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递归获取树层次结构的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！