sql - 我怎样才能编写一个程序来获得级别 i 到 j 或像这样的树？

Question

我有一张 table

        Users
-------------------------
id | ancestor_id | ....
-------------------------
 1 |    NULL     | ....
 2 |     1       | ....
 3 |     1       | ....
 4 |     3       | ....
 5 |     3       | ....

代表一棵树

   level 1           1
                   /  \ 
   level 2        2    3 
                      / \
   level 3           4   5

我想创建一个返回给定用户的第 i 到 j 代后代的过程:

CREATE PROCEDURE DescendantsLevel 
   @user_id INT,
   @i INT,
   @j INT
AS
   ....

但是，如果 @j 为 NULL，它会返回从 @i 代开始的所有后代。

例子:

EXEC DescendantLevel @user_id=1,@i=2,@j=NULL

会回来

-------------------------
id | ancestor_id | ....
-------------------------
 1 |    NULL     | ....
 2 |     1       | ....
 3 |     1       | ....
 4 |     3       | ....
 5 |     3       | ....

和

EXEC DescendantLevel @user_id=1,@i=1,@j=2

会回来

        Users
-------------------------
id | ancestor_id | ....
-------------------------
 1 |    NULL     | ....
 2 |     1       | ....
 3 |     1       | ....

几个问题，我有:

• 是否有比 NULL 更好的值来表示 SQL 中“无限”的某些概念？
• 我如何实现我所描述的程序？
• 是否有更好的数据库设计方法来简化流程？

Answer 1

使用递归 CTE:

DECLARE @test TABLE (id INT NOT NULL, ancestor_id INT NULL)

DECLARE
    @id INT = 1,
    @i INT = 1,
    @j INT = 2

INSERT INTO @test (id, ancestor_id)
VALUES
    (1, NULL),
    (2, 1),
    (3, 1),
    (4, 3),
    (5, 3)

;WITH CTE_Tree AS
(
    SELECT
        id,
        ancestor_id,
        1 AS lvl,
        id AS base
    FROM
        @test
    WHERE
        id = @id
    UNION ALL
    SELECT
        C.id,
        C.ancestor_id,
        P.lvl + 1 AS lvl,
        P.base AS base
    FROM
        CTE_Tree P
    INNER JOIN @test C ON C.ancestor_id = P.id
    WHERE
        lvl <= COALESCE(@j, 9999)
)
SELECT
    id,
    ancestor_id
FROM
    CTE_Tree
WHERE
    lvl BETWEEN @i AND COALESCE(@j, 9999)

这依赖于不超过 9999 级的递归(实际上 SQL Server 的默认递归限制是 100 级，所以超过 100 级就会出错)。