SQL Server - 如何管理表中的分层数据？

Question

我使用 SQL Server 2000。

假设我有两个如下所示的表：

Area
----------------------------------
ID| Name   | HierarchyLevel
----------------------------------
1 | World  |     1
2 | America|     2
3 | Europe |     2
4 | Africa |     2
5 | USA    |     3

AreaHierarchy.ParentID

AreaHierarchy
------------------------
ID | ParentID | ChildID
------------------------
 1 |   1      |    2
 2 |   1      |    3
 3 |   1      |    4
 4 |   2      |    5

其中

和 AreaHierarchy.ChildID 是 Area.ID 的 FK

我怎样才能找到 USA 的第 n 个父级？

不循环可以吗？

可能不会。

Answer 1

没有循环，没有递归

最好的办法是在第二个表中添加附加字段，该字段将被称为 ie。 Parents 并且只需将父 ID 存储在字符串中，例如：

AreaHierarchy
------------------------------------
ID | ParentID | ChildID | Parents
------------------------------------
 1 |    1     |    2    | 1/
 2 |    1     |    3    | 1/
 3 |    1     |    4    | 1/
 4 |    2     |    5    | 1/2/

这样您就可以轻松访问分支中的任何父级，而无需递归或任何其他复杂的过程。处理成本非常小，您只需复制父项的 Parents 值并再添加一个 ID。由于您可能需要读取的内容多于写入/更新的内容，因此这是解决您的问题的最佳解决方案。

如果我是你，我只会为你拥有的数据保留一张表。将两个表合并为一个表。级别也可以根据 Parents varchar 值中的斜杠计数来计算，但我不建议这样做。

您应该注意的其他“问题”

如果您的数据主要是读/写，更新较少，那么这种结构确实非常高效。但是，如果您的表执行的更新次数多于读/写次数，则应避免使用此技术。为什么？想象一下，你有一棵很深的树，里面有很多孩子。更改根附近某个较高节点的父节点意味着您应该更新整个子树节点的父节点。

Answer 2

应该有效

CREATE PROCEDURE find_nth_parent 
    @id INT,
    @level INT
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @counter INT
    SET @counter = 1

    DECLARE @currentItem INT
    DECLARE @currentItemNew INT

    SET @currentItem = @id

    WHILE @counter <= @level
    BEGIN
        SET @currentItemNew = NULL
        SELECT @currentItemNew = ParentID FROM AreaHierarchy WHERE ChildId = @currentItem
        IF @currentItemNew IS NULL
        BEGIN
            SELECT NULL
            RETURN 
        END
        SET @currentItem = @currentItemNew
        SET @counter = @counter + 1
    END
    SELECT @currentItem
END

调用

EXEC find_nth_parent 5,2

返回 1 表示“世界”（第二个父级），调用

EXEC find_nth_parent 5,1

返回 2 表示“美国”（第一个父级）。

希望有帮助

Answer 3

您可以使用递归。如果您有 SQL Server 2005 或更高版本，则可以使用公用表表达式。如果不是，您实际上需要使用用户定义的函数。

执行此操作的 UDF 示例可能是...

CREATE FUNCTION get_nth_parent(area_id AS INT, n as INT)
RETURNS INT
AS

IF (n = 0) RETURN area_id

DECLARE @return INT
SELECT
   @return = dbo.get_nth_parent(AreaHierarchy.ParentID, n-1)
FROM
   AreaHierarchy
WHERE
   ChildID = area_id

RETURN @return

使用公共表表达式的示例可能是...

DECLARE @hierarchy TABLE (
   parent_id  INT,
   child_id   INT
)
INSERT INTO @hierarchy SELECT 1,2
INSERT INTO @hierarchy SELECT 1,3
INSERT INTO @hierarchy SELECT 1,4
INSERT INTO @hierarchy SELECT 2,5


;WITH
   relative_distance (
      child_id,
      parent_id,
      distance
   )
AS
(
   SELECT
      child_id,
      parent_id,
      1
   FROM
      @hierarchy

   UNION ALL

   SELECT
      [relative_distance].child_id,
      [hierarchy].parent_id,
      [relative_distance].distance + 1
   FROM
      [relative_distance]
   INNER JOIN
      @hierarchy AS [hierarchy]
         ON [hierarchy].child_id = [relative_distance].parent_id
)

SELECT
   parent_id
FROM
   [relative_distance]
WHERE
   child_id = 5
   AND distance = 2

Answer 4

在 SQL Server 2005+ 中，您可以在函数中使用 CTE：

create function get_parent(@child as int, @parent_level as int)
returns int
as
begin
    declare @parent int

    ;with parentage as (
         select 
             h.parent_id, 
             h.child_id,
             0 as level
         from 
             areahierarchy h
         where
             h.child_id = @child
         union all
         select
             h.parent_id,
             h.child_id,
             p.level + 1 as level
         from
             areahierarchy h
             inner join parentage p on
                 h.parent_id = p.child_id
         where
             p.level < @parent_level
    )

    select @parent = p.child_id from parentage p 
    where level = (select max(level) from parentage)

    return @parent
end

Answer 5

我知道您希望支持 SQL Server 2000，但我认为应该注意的是，SQL Server 2008 层次结构 ID 函数 GetAncestor() 正是您所寻找的。

Answer 6

您可以使用 Joe Celko 的嵌套集合模型 https://en.wikipedia.org/wiki/Nested_set_model

甚至更好的闭包表模型