SQL Server的树分层结构和嵌套设置与重复记录ID(SQL Server Tree Hiera

Given that I have this resultset structure (superfluous fields have been stripped)

Id | ParentId | Name | Depth
----------------------------

is it possible to have the records returned in tree order i.e. Parent then Children, if a Child is a Parent, then their Children, if not then Sibling, etc? For example,

Id | ParentId | Name | Depth
----------------------------
1    NULL       Major    1
2    1          Minor    2
3    1          Minor    2
4    3          Build    3
5    3          Build    3
6    1          Minor    2

/* etc, etc */

The only way that I can think of doing this would be to follow this article -

Improve hierarchy performance using nested sets

and include [LeftExtent] and [RightExtent] fields against each record. Now the SQL in the article works fine when Ids are unique, but in this particular tree structure, a record with the same Id can appear in different places within the tree (the ParentId field is different, obviously). I think the problem is in this SQL from the article -

  INSERT INTO @tmpStack
    (
      EmployeeID, 
      LeftExtent
    )
  SELECT TOP 1 EmployeeID, @counter 
  FROM Employee 
  WHERE ISNULL(ParentID, 0) = ISNULL(@parentid,0) 
  /* If the Id has already been added then record is not given [LeftExtent] or [RightExtent] values. */
  AND EmployeeID NOT IN (SELECT EmployeeID FROM @tmpStack)

How can this be altered to allow records with duplicate Ids to be given [LeftExtent] and [RightExtent] values, or I am completely missing an easier way to return the resultset in the order that I require?

Answer 1:

这里有一个做的伎俩对我来说：

@ParentID只是在层次结构的起点，但你可以在0传递（但我认为你正在使用null作为基础ID，所以你会得到的想法）

有序排列的关键字是与同时建立起来的排序键。

WITH RoleHierarchy (RoleID, [Role], [Description], ParentID, Editable, HierarchyLevel, SortKey) AS
(
   -- Base
   SELECT
        RoleID,
        [Role],
        [Description],
        ParentID,
        Editable,
        0 as HierarchyLevel,
        CAST(RoleID AS VARBINARY(300))
   FROM
        dbo.Roles       
   WHERE
        RoleID = @ParentID

   UNION ALL

   -- Recursive
   SELECT
        e.RoleID,
        e.[Role],
        e.[Description],
        e.ParentID,
        e.Editable,
        th.HierarchyLevel + 1 AS HierarchyLevel,
        CAST (th.SortKey + CAST (e.[Role] AS VARBINARY(100)) + CAST (e.[RoleID] AS VARBINARY(100)) AS VARBINARY(300))
   FROM
        Roles e
        INNER JOIN RoleHierarchy th ON e.ParentID = th.RoleID
    WHERE
        e.RoleID != 0
)

SELECT
    RoleID,
    ParentID,
    [Role],
    [Description],
    Editable,
    HierarchyLevel
FROM
    RoleHierarchy
WHERE
    RoleID != @ParentID
ORDER BY
    SortKey

Answer 2:

你应该看看在SQL Server 2005中递归公用表表达式：

http://www.4guysfromrolla.com/webtech/071906-1.shtml
http://www.sqlservercentral.com/articles/Development/recursivequeriesinsqlserver2005/1760/

在你的情况，这将是这样的：

WITH EmployeeCTE AS
(
   -- get the anchor
   SELECT ID, ParentID, Name, 0 as 'Depth'
   FROM Employee WHERE ParentID IS NULL

   -- recursively union lower levels
   UNION ALL
   SELECT e.ID, e.ParentID, e.Name, e.Depth+1
   FROM Employee e
   INNER JOIN EmployeeCTE ON e.ParentID = EmployeeCTE.ID
)
SELECT * FROM EmployeeCTE

这应该给你一个很好的查询结果与您要查找的数据集。还是我失去了一些东西？

渣