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 Pathcolumn....

此处 SQLFiddle 带有数据示例

这篇关于CTE递归以获取树层次结构的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！