CTE递归以获取树层次结构

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

我需要以特定的方式获取树的有序层次结构。有问题的表看起来像这样（所有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 带有数据示例