SQL Server 树层次结构和具有重复记录 ID 的嵌套集 [英] SQL Server Tree Hierarchy and Nested Sets with Duplicate Record ids

问题描述

鉴于我有这个结果集结构(多余的字段已被剥离)

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

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

是否可以按树顺序返回记录，即 Parent 然后 Children，如果 Child 是 Parent，然后是他们的 Children，如果不是，则是 Sibling，等等?例如，

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 -

使用嵌套集提高层次结构性能

并针对每条记录包含 [LeftExtent] 和 [RightExtent] 字段.现在，当 Ids 是唯一的时，文章中的 SQL 可以正常工作，但是在这种特殊的树结构中，具有相同 Id 的记录可以出现在树中的不同位置(ParentId 字段显然不同).我认为问题出在这篇文章中的 SQL -

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) 

如何更改以允许为具有重复 Ids 的记录提供 [LeftExtent] 和 [RightExtent] 值，或者我完全缺少一种更简单的方法来按我的顺序返回结果集需要吗?

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?

推荐答案

这里有一个对我有用的方案:

Here's one that does the trick for me:

@ParentID 只是层次结构中的一个起点，但您可以传入 0(但我认为您使用 null 作为基本 ID，因此您会明白这一点)

@ParentID is just a starting point in the hierarchy, but you can pass in 0 (but I think you're using null as the base ID, so you'll get the idea)

有序排序的关键是建立起来的排序键.

The key to ordered sorting is with the sort key that's built up.

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

这篇关于SQL Server 树层次结构和具有重复记录 ID 的嵌套集的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！