Альтернативы рекурсивному CTE внутри коррелированного подзапроса «существует»?

Один из способов сделать это — создать рекурсивное CTE, в котором для каждого пользователя есть по одной строке для каждого предка этого пользователя в дереве. Затем вы можете использовать CTE для фильтрации предков. Например, с этим деревом:

Bob
|-Alice
  |-Jim

CTE вернет что-то вроде:

User  Ancestor Level
----  -------- -----
Bob   NULL     1
Alice Bob      1
Jim   Alice    1
Jim   Bob      2

Столбец Level оказался не очень важным, но я обнаружил, что это помогло, когда я писал запрос.

Вот пример скрипта, который идентифицирует всех пользователей, которые находятся под Алисой в иерархии:

CREATE TABLE Users(
    UserId int NOT NULL PRIMARY KEY,
    Name nvarchar(25),
    ManagerId int
);
GO

INSERT INTO Users (UserId, Name, ManagerId)
SELECT 1, 'Bob', NULL UNION ALL
SELECT 2, 'Steve', 1 UNION ALL
SELECT 3, 'Chris', 2 UNION ALL
SELECT 4, 'Alice', 1 UNION ALL
SELECT 5, 'Roger', 4 UNION ALL
SELECT 6, 'Tony', 5;
GO

WITH all_ancestors AS (
    SELECT
        u.UserId,
        u.Name,
        u.ManagerId AS AncestorId,
        1 AS level
    FROM
        Users AS u
    UNION ALL
    SELECT
        alla.UserId,
        alla.Name,
        u.ManagerId AS AncestorId,
        alla.level + 1
    FROM
            all_ancestors AS alla
        INNER JOIN
            Users AS u
        ON
            alla.AncestorId = u.UserId
)
SELECT
    u.*
FROM
        Users AS u
    INNER JOIN
        all_ancestors AS a
    ON
        u.UserId = a.UserId
WHERE
    a.AncestorId = 4; -- Alice
GO

DROP TABLE Users;
GO

Сколько уровней иерархии может быть в таблице Users? Я ожидаю, что он будет достаточно низким. Интересно, достаточно ли это низко, чтобы попробовать несколько вложенных тестов EXISTS, например:

… /* your main query here */
WHERE …
  AND EXISTS (
    SELECT *
    FROM [Users] u1
    WHERE [UserID] = @UserID
      AND (
        [ManagerId] = [Rules].[RuleAddedByUserId]
        OR EXISTS (
          SELECT *
          FROM [Users] u2
          WHERE [UserID] = u1.[ManagerID]
            AND (
              [ManagerId] = [Rules].[RuleAddedByUserId]
              OR EXISTS (
                SELECT *
                FROM [Users] u3
                WHERE [UserID] = u2.[ManagerID]
                  AND (
                    [ManagerId] = [Rules].[RuleAddedByUserId]
                    OR EXISTS ( … /* and so on, until you've covered
                                     all possible levels */
                    )
                  )
              )
            )
        )
      )
  )

Я добрался туда в конце концов! Спасибо, ребята, за помощь.

Вот фрагмент SQL, над которым я работаю.

Мне просто нужно было изменить свое мышление, вместо того, чтобы видеть, существует ли пользователь под менеджером в предложении where. Мне нужно было рассматривать CTE как предварительный фильтр и создавать все детали, которые мне нужны, заранее, а затем выполнять обычную фильтрацию после, т.е. существующих операторов (примечание: я не включил их для краткости).

RulesUserHierarchy(UserId, ManagerId, PushRuleId, OnlyForSubOrdinates) -- Gets only subordinates for rules created by managers. And all users for those created by admin.
AS
(
    --Anchor Definition
    SELECT
         [Users].[UserId]
        ,[Users].[ManagerId]
        ,[RulesAnchor].[PushRuleId]
        ,[RulesAnchor].[OnlyForSubOrdinates]
    FROM [Users]
        CROSS JOIN [Rules] [RulesAnchor] --assume every user is doing every rule at this point (because the recursive statement has to be the first statement), we'll filter later.
    WHERE (([OnlyForSubOrdinates]) = 0 OR ([OnlyForSubOrdinates] = 1 AND [UserId] = [RulesAnchor].[AddedByUserId]))
    UNION ALL

    --Recursive Member definiation
    SELECT
         [Users].[UserId]
        ,[Users].[ManagerId]
        ,[RulesUserHierarchy].[PushRuleId]
        ,[RulesUserHierarchy].[OnlyForSubOrdinates]
    FROM [Users]
        INNER JOIN [RulesUserHierarchy]
            ON [Users].[ManagerId] = [RulesUserHierarchy].[UserId] --recursive hook 
            AND [RulesUserHierarchy].[OnlyForSubOrdinates] = 1 -- no point recursing if it's for everyone, as the anchor will pull back everything for us.
    WHERE [Users].[UserId] <> [Users].[ManagerId] --don't recurse if the anchor definition matches itself (to avoid an infinate loop).
)       
-- simple statement to test recursion above, will be filtering the inclusions here (e.g. the other mega exists statements)
SELECT [UserId], [ManagerId], [PushRuleId], [OnlyForSubOrdinates] FROM [RulesUserHierarchy]

ИЗМЕНИТЬ

Я понял, что мне не нужно перекрестное соединение в рекурсивном определении. Хотя это не повлияет на результат последних операторов exists, это не очень хорошо для производительности.