CTE递归获取树层次结构

本文介绍了CTE递归获取树层次结构的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

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