SQL Server 2005 递归查询与数据循环 - 可能吗？

Question

我有一个标准的老板/下属员工表。 我需要选择一个老板（由 ID 指定）和他的所有下属（及其下属等）。 不幸的是，现实世界的数据存在一些循环（例如，两个公司所有者都将对方设置为自己的老板）。 带有 CTE 的简单递归查询对此感到窒息（超出了最大递归级别 100）。 员工还能选拔吗？ 我不关心它们被选择的顺序，只关心它们每个被选择一次。

---

Added: You want my query? Umm... OK... I though it is pretty obvious, but - here it is:

with
UserTbl as -- Selects an employee and his subordinates.
(
    select a.[User_ID], a.[Manager_ID] from [User] a WHERE [User_ID] = @UserID
    union all
    select a.[User_ID], a.[Manager_ID] from [User] a join UserTbl b on (a.[Manager_ID]=b.[User_ID])
)
select * from UserTbl

---

Added 2: Oh, in case it wasn't clear - this is a production system and I have to do a little upgrade (basically add a sort of report). Thus, I'd prefer not to modify the data if it can be avoided.

Answer 1

这是我在一个项目中使用的代码，用于上下追踪层次关系树。

用户定义的函数来捕获下属：

CREATE FUNCTION fn_UserSubordinates(@User_ID INT)
RETURNS @SubordinateUsers TABLE (User_ID INT, Distance INT) AS BEGIN
    IF @User_ID IS NULL
        RETURN

    INSERT INTO @SubordinateUsers (User_ID, Distance) VALUES ( @User_ID, 0)

    DECLARE @Distance INT, @Finished BIT
    SELECT @Distance = 1, @Finished = 0

    WHILE @Finished = 0
    BEGIN
        INSERT INTO @SubordinateUsers
            SELECT S.User_ID, @Distance
                FROM Users AS S
                JOIN @SubordinateUsers AS C
                    ON C.User_ID = S.Manager_ID
                LEFT JOIN @SubordinateUsers AS C2
                    ON C2.User_ID = S.User_ID
                WHERE C2.User_ID IS NULL
        IF @@RowCount = 0
            SET @Finished = 1

        SET @Distance = @Distance + 1
    END

    RETURN
END

用户定义的函数来捕获经理：

CREATE FUNCTION fn_UserManagers(@User_ID INT)
RETURNS @User TABLE (User_ID INT, Distance INT) AS BEGIN
    IF @User_ID IS NULL
        RETURN

    DECLARE @Manager_ID INT

    SELECT @Manager_ID = Manager_ID
    FROM UserClasses WITH (NOLOCK)
    WHERE User_ID = @User_ID

    INSERT INTO @UserClasses (User_ID, Distance)
        SELECT User_ID, Distance + 1
        FROM dbo.fn_UserManagers(@Manager_ID)

    INSERT INTO @User (User_ID, Distance) VALUES (@User_ID, 0)

    RETURN
END

Answer 2

更好的解决方案是清理数据并确保将来不会出现任何循环 - 这可以通过触发器或包含在检查约束中的 UDF 来完成。

但是，您可以使用多语句 UDF，如我在此处演示的： 避免无限循环。 第一部分

您可以在连接中添加 NOT IN() 子句来过滤掉循环。

Answer 3

我可以想到两种方法。

1）生成比您想要的更多的行，但要进行检查以确保它不会递归得太深。 然后删除重复的用户记录。

2）使用字符串来保存已经访问过的用户。 就像不在子查询中的想法不起作用一样。

方法 1：

; with TooMuchHierarchy as (
    select "User_ID"
        , Manager_ID 
        , 0 as Depth
    from "User" 
    WHERE "User_ID" = @UserID
    union all
    select U."User_ID"
        , U.Manager_ID
        , M.Depth + 1 as Depth
    from TooMuchHierarchy M
    inner join "User" U 
        on U.Manager_ID = M."user_id"
    where Depth < 100) -- Warning MAGIC NUMBER!!
, AddMaxDepth as (
    select "User_ID"
        , Manager_id
        , Depth
        , max(depth) over (partition by "User_ID") as MaxDepth
    from TooMuchHierarchy)
select "user_id", Manager_Id 
from AddMaxDepth
where Depth = MaxDepth

行 where Depth < 100 可以防止出现最大递归错误。 减小这个数字，就会产生更少需要丢弃的记录。 如果它太小，员工将不会被返回，因此请确保它至少与所存储的组织结构图的深度一样大。 随着公司的发展，维护有点像噩梦。 如果需要更大，则将选项（maxrecursion ... number ...）添加到整个事物中以允许更多递归。

方法二：

; with Hierarchy as (
    select "User_ID"
        , Manager_ID 
        , '#' + cast("user_id" as varchar(max)) + '#' as user_id_list
    from "User" 
    WHERE "User_ID" = @UserID
    union all
    select U."User_ID"
        , U.Manager_ID
        , M.user_id_list + '#' + cast(U."user_id" as varchar(max)) + '#' as user_id_list
    from Hierarchy M
    inner join "User" U 
        on U.Manager_ID = M."user_id"
    where user_id_list not like '%#' + cast(U."User_id" as varchar(max)) + '#%')
select "user_id", Manager_Id 
from Hierarchy

Answer 4

基本上，如果数据中有这样的循环，您就必须自己执行检索逻辑。
你可以使用一个 cte 来吸引下属，而使用另一个 cte 来吸引老板。

另一个想法是为两个公司所有者设立一个虚拟的老板，这样他们就不会成为彼此的老板，这是荒谬的。 这是我的首选。

Answer 5

我知道您不久前问过这个问题，但这里有一个解决方案可以用于检测无限递归循环。 我生成一个路径，并在 CTE 条件中检查用户 ID 是否在路径中，如果是，则不会再次处理它。 希望这可以帮助。

何塞

DECLARE @Table TABLE(
    USER_ID INT,
    MANAGER_ID INT )
INSERT INTO @Table (USER_ID,MANAGER_ID) SELECT 1, 2
INSERT INTO @Table (USER_ID,MANAGER_ID) SELECT 2, 1
INSERT INTO @Table (USER_ID,MANAGER_ID) SELECT 3, 1
INSERT INTO @Table (USER_ID,MANAGER_ID) SELECT 4, 3
INSERT INTO @Table (USER_ID,MANAGER_ID) SELECT 5, 2

DECLARE @UserID INT
SELECT @UserID = 1

;with
UserTbl as -- Selects an employee and his subordinates.
(
    select 
        '/'+cast( a.USER_ID as varchar(max)) as [path],
        a.[User_ID], 
        a.[Manager_ID] 
    from @Table a 
    where [User_ID] = @UserID
    union all
    select
        b.[path] +'/'+ cast( a.USER_ID as varchar(max)) as [path],
        a.[User_ID], 
        a.[Manager_ID] 
    from @Table a 
    inner join UserTbl b 
        on (a.[Manager_ID]=b.[User_ID])
    where charindex('/'+cast( a.USER_ID as varchar(max))+'/',[path]) = 0
)
select * from UserTbl

Answer 6

您不必递归地执行此操作。 它可以在 WHILE 循环中完成。 我保证它会更快：每次我对这两种技术进行计时时，这对我来说都是如此。 这听起来效率低下，但事实并非如此，因为循环次数就是递归级别。 在每次迭代中，您都可以检查循环并纠正发生循环的位置。 您还可以对临时表施加约束，以便在发生循环时引发错误，尽管您似乎更喜欢更优雅地处理循环的方法。 当 while 循环迭代一定数量的级别时，您还可以触发错误（以捕获未检测到的循环？ - 哦，天哪，有时会发生这种情况。

诀窍是重复插入到临时表中（该表由根条目填充） ），包括具有当前迭代编号的列，并在临时表中的最新结果和原始表中的子条目之间进行内连接，只需在 @@rowcount=0 时跳出循环即可！
简单吧？

Answer 7

不是通用的解决方案，但可能适合您的情况：在您的选择查询中将其修改

select a.[User_ID], a.[Manager_ID] from [User] a join UserTbl b on (a.[Manager_ID]=b.[User_ID])

为：

select a.[User_ID], a.[Manager_ID] from [User] a join UserTbl b on (a.[Manager_ID]=b.[User_ID]) 
   and a.[User_ID] <> @UserID

Answer 8

这适用于初始递归链接，但可能不适用于较长的链接，

DECLARE @Table TABLE(
        ID INT,
        PARENTID INT
)

INSERT INTO @Table (ID,PARENTID) SELECT 1, 2

INSERT INTO @Table (ID,PARENTID) SELECT 2, 1

INSERT INTO @Table (ID,PARENTID) SELECT 3, 1

INSERT INTO @Table (ID,PARENTID) SELECT 4, 3

INSERT INTO @Table (ID,PARENTID) SELECT 5, 2


SELECT * FROM @Table

DECLARE @ID INT

SELECT @ID = 1

;WITH boss (ID,PARENTID) AS (
    SELECT  ID,
            PARENTID
    FROM    @Table
    WHERE   PARENTID = @ID
),
 bossChild (ID,PARENTID) AS (
    SELECT  ID,
            PARENTID
    FROM    boss
    UNION ALL
    SELECT  t.ID,
            t.PARENTID
    FROM    @Table t INNER JOIN
            bossChild b ON t.PARENTID = b.ID
    WHERE   t.ID NOT IN (SELECT PARENTID FROM boss)
)
SELECT  *
FROM    bossChild
OPTION (MAXRECURSION 0)

我建议使用 while 循环，并且仅在 id 不存在时才将链接插入到临时表中，从而消除无限循环。

Answer 9

我知道已经有一段时间了，但我认为我应该分享我的经验，因为我尝试了每一个解决方案，这里是我的发现摘要（也许是这篇文章？）：

• 添加具有当前路径的列确实有效，但性能受到影响所以这对我来说不是一个选择。
• 我找不到使用 CTE 的方法。
• 我编写了一个递归 SQL 函数，它将employeeIds 添加到表中。 为了避免循环引用，需要进行检查以确保没有重复的 ID 添加到表中。 表现一般，但并不理想。

完成所有这些后，我想到了将[合格]员工的整个子集转储到代码（C#）中并使用递归方法对其进行过滤的想法。 然后，我将筛选后的员工列表写入数据表，并将其作为临时表导出到我的存储过程中。 令我难以置信的是，事实证明，对于小型表和相对较大的表（我尝试了最多 35,000 行的表）来说，这都是最快、最灵活的方法。