CTE递归获取树层次结构

我需要以特定的方式获得树的有序层次结构。 有问题的表看起来有点像这样（所有ID字段都是uniqueidentifiers，为简单起见我简化了数据）：

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

树结构的图形视图（*表示'服务'）：

           A
       ___/ ___
      /         
    1            4
   /           / 
  2   7*       5   8
 /            /
3*           6*

使用这个查询，我可以得到层次结构（假设'A'是一个唯一标识符，我知道它不是现实生活中的）：

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

这给了我估计ID'A'的孩子，但是按照它出现在表格中的顺序。 即：

EstimateItemID
--------------
      1
      2
      3
      4
      5
      6
      7
      8

不幸的是，我需要的是一个有序的层次结构，其结果集遵循以下约束条件：

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

所以，在这个例子中，我需要结果的顺序是：

EstimateItemID
--------------
      1
      2
      3
      7
      4
      5
      8
      6

我需要添加到我的查询来完成此任务？

---

尝试这个：

;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为每个级别排序子节点，你可以使用Level和SUBSTRING of Path列....

这里SQLFiddle带有数据样本

---

这是Fabio上面的好主意的补充。 就像我在回复他原来的帖子时所说的那样。 我已经使用更常见的数据，表名和字段重新发布了他的想法，以便其他人更容易遵循。

谢谢法比奥！ 伟大的名字。

首先要处理一些数据：

CREATE TABLE tblLocations (ID INT IDENTITY(1,1), Code VARCHAR(1), ParentID INT, Name VARCHAR(20));

INSERT INTO tblLocations (Code, ParentID, Name) VALUES
('A', NULL, 'West'),
('A', 1, 'WA'),
('A', 2, 'Seattle'),
('A', NULL, 'East'),
('A', 4, 'NY'),
('A', 5, 'New York'),
('A', 1, 'NV'),
('A', 7, 'Las Vegas'),
('A', 2, 'Vancouver'),
('A', 4, 'FL'),
('A', 5, 'Buffalo'),
('A', 1, 'CA'),
('A', 10, 'Miami'),
('A', 12, 'Los Angeles'),
('A', 7, 'Reno'),
('A', 12, 'San Francisco'),
('A', 10, 'Orlando'),
('A', 12, 'Sacramento');

现在递归查询：

-- Note: The 'Code' field isn't used, but you could add it to display more info.
;WITH MyCTE AS (
  SELECT ID, Name, 0 AS TreeLevel, CAST(ID AS VARCHAR(255)) AS TreePath
  FROM tblLocations T1
  WHERE ParentID IS NULL

  UNION ALL

  SELECT T2.ID, T2.Name, TreeLevel + 1, CAST(TreePath + '.' + CAST(T2.ID AS VARCHAR(255)) AS VARCHAR(255)) AS TreePath
  FROM tblLocations T2
  INNER JOIN MyCTE itms ON itms.ID = T2.ParentID
)
-- Note: The 'replicate' function is not needed. Added it to give a visual of the results.
SELECT ID, Replicate('.', TreeLevel * 4)+Name 'Name', TreeLevel, TreePath
FROM  MyCTE 
ORDER BY TreePath;

---

我相信你需要将以下内容添加到CTE的结果中...

BranchID =某种标识符，用于唯一标识分支。 原谅我不是更具体，但我不确定什么标识分支为您的需求。 您的示例显示了一个二叉树，其中所有分支都流回根目录。

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

父母=识别父母。

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

链接地址: http://www.djcxy.com/p/60229.html

上一篇: CTE Recursion to get tree hierarchy

下一篇: Using SQL server hierarchyid how to aggregate leaf node values at ancestor level