CTE递归以获取树层次结构

Question

我需要以特定方式获取树的有序层次结构。有问题的表格看起来像这样（所有ID字段都是uniqueidentifier，为了示例，我简化了数据）：EstimateItemID EstimateIDParentEstimateItemID ItemType-------------- ---------- -------------------- ------ -       1个NULL乘积       2 A 1产品       3 A 2服务       4 NULL产品       5 A 4产品       6 A 5服务       7 A 1服务       8 A 4产品树形结构的图形视图（*表示“服务”）：           一种       ___ / \ ___      / \    1 4   ///  2 7 * 5 8 ///3 * 6 *使用此查询，我可以得到层次结构（假装'A'是唯一标识符，我知道它不是现实生活中的）：DECLARE @EstimateID uniqueidentifierSELECT @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'的子级，但是按照它在表格中出现的顺序。即：EstimateItemID--------------      1个      2      3      4      5      6      7      8不幸的是，我需要的是一个有序集合，其结果集遵循以下约束：1.每个分支必须分组2. ItemType为“ product”且父级为最高的记录 3.具有ItemType'product'和非NULL父级的记录分组在顶部节点之后 4. ItemType为“ service”的记录是分支的底部节点因此，在此示例中，我需要结果的顺序为：EstimateItemID--------------      1个      2      3      7      4      5      8      6要完成此操作，我需要添加什么？

Answer 1

尝试这个：

;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

带Path-行由父节点排序

如果要按ItemType每个级别对子节点进行排序，则可以使用Leveland SUBSTRINGof Pathcolumn ....

这里是SQLFiddle与数据样本

Answer 2

我相信您需要在CTE的结果中添加以下内容...

BranchID =唯一标识分支的某种标识符。原谅我没有具体说明，但我不确定是什么确定了您需要的分支。您的示例显示了一个二叉树，其中所有分支都流回到根。

ItemTypeID，其中（例如）0 =产品和1 =服务。

父母=标识父母。

如果输出中存在这些内容，我认为您应该能够将查询的输出用作另一个CTE或查询中的FROM子句。按BranchID，ItemTypeID，Parent排序。