Alternatives à une CTE récursive à l'intérieur d'une sous-requête corrélée «existante»?

Combien de niveaux de hiérarchie peut-il y avoir dans la table code> code>? Je m'attendrais à ce qu'il soit raisonnablement bas. Je me demande s'il est assez faible d'essayer plusieurs tests code> imbriqués code>, comme celui-ci:

… /* 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 */
                    )
                  )
              )
            )
        )
      )
  )

Une façon de le faire serait de créer un CTE récursif qui a, pour chaque utilisateur, une ligne pour chaque ancêtre de cet utilisateur dans l'arbre. Ensuite, vous pouvez utiliser le CTE pour filtrer pour les ancêtres. Par exemple, avec cet arbre:

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

Je suis arrivé là-bas finalement! Merci les gars pour l'aide.

Voici un extrait de la SQL que je travaille sur. P>

Je devais juste changer ma pensée, au lieu de voir si un utilisateur existe sous un gestionnaire dans le lieu où clause. J'avais besoin de considérer le CTE un pré-filtre et de construire tous les détails dont j'avais besoin avant la main, puis faites mon filtrage normal après les déclarations existantes (NB que je n'ai pas inclus celles de la brièveté). P>

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]