SQL Server树形层次结构和嵌套集合中的重复记录ID

Question

SQL Server树形层次结构和嵌套集合中的重复记录ID

sqlsql-serversql-server-2005treenested-sets

3

鉴于我有这个结果集结构（已剥离多余字段），请参见以下内容：

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

是否可以按照树形顺序返回记录，即首先是Parent，然后是Children，如果一个Child是Parent，那么再是他们的Children，否则就是Sibling等等？例如：

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 */

我能想到的唯一方法就是按照这篇文章的方式进行操作 - 使用嵌套集提高层次结构性能并且在每个记录中包含[LeftExtent]和[RightExtent]字段。现在，当Ids是唯一的时，该文章中的SQL工作正常，但在这种特定的树形结构中，具有相同Id的记录可以出现在树的不同位置（显然，ParentId字段不同）。我认为问题出在这篇文章中的SQL中 -

  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)

如何更改此代码以允许具有重复Ids的记录具有[LeftExtent]和[RightExtent]值，或者我完全错过了以我所需的顺序返回结果集的更简单的方法？

- Russ Cam

3个回答

3

您应该查看 SQL Server 2005 中递归公共表达式的用法：

在您的情况下，这将是类似于：

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

这应该会为您提供一个不错的查询结果集，其中包含您要查找的数据。或者我有什么遗漏吗？

Marc

- marc_s

我目前有一个CTE，用于递归连接记录以构建结果集。这样做很好，但问题在于结果集中的记录顺序不是我想要的顺序，正如问题中指定的那样。我将修改问题，将结果集结构作为说明而不是表结构。 - Russ Cam

0

如果你使用了物化路径或者HIERARCHYID，你的生活会变得更加轻松...

- A-K

HIERARCHYID 看起来很有趣，但不幸的是，解决方案是使用 SQL Server 2005。 - Russ Cam

物化路径在2005年是可行的，它可以让你的生活更加轻松。 - A-K

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- ScottE · Accepted Answer

这里有一个对我很有用的方法：

@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