在分层查询中获取组最后/最大记录的可能方法？

Question

假设我有一个像这样的表：

CREATE TABLE user_delegates (
    [id] INT IDENTITY(1,1) NOT NULL,
    [user_from] VARCHAR(10) NOT NULL,
    [user_to] VARCHAR(10) NOT NULL,
    CONSTRAINT [PK_user_delegates] PRIMARY KEY CLUSTERED ([id] ASC),
    CONSTRAINT [UK_user_delegates] UNIQUE ([user_from] ASC)
)

因此，用户 A 必须有权将她的系统访问权限委托给另一个用户 B。当她这样做时，她将无法再访问系统 - 用户 B 将不得不“中断” “在她能够再次使用系统之前进行该委托...

但还要考虑到，如果用户 B 将访问权限委托给用户 C，则用户 C 也将开始模拟用户 A，依此类推。

（我知道这似乎是一场安全噩梦 - 请让我们忘记这一点，好吗？:-)）

还要考虑这些记录：

INSERT INTO user_delegates([user_from], [user_to]) values ('ANTHONY', 'JOHN')
INSERT INTO user_delegates([user_from], [user_to]) values ('JOHN', 'JOHN')
INSERT INTO user_delegates([user_from], [user_to]) values ('KARL', 'JOSHUA')
INSERT INTO user_delegates([user_from], [user_to]) values ('JOSHUA', 'PIOTR')
INSERT INTO user_delegates([user_from], [user_to]) values ('PIOTR', 'HANS')

所以我需要的是找到最后（这意味着活动的）每个用户的委托。

我已经找到了一个解决方案，我决定不在这里展示（除非每个人都忽略我，这总是有可能的）。我只能说，这是一个有点长的答案，而且看起来确实像是用大炮杀死跳蚤……

但是你会怎么做呢？考虑任何可用的相关 SQL Server 扩展，并注意我们正在寻找一个既优雅又具有良好性能的答案...

顺便说一句，这是预期的结果集：

id          user_from  user_to   
----------- ---------- ----------
1           ANTHONY    JOHN      
2           JOHN       JOHN      
3           KARL       HANS      
4           JOSHUA     HANS      
5           PIOTR      HANS      

(5 row(s) affected)

提前致谢！

Answer 1

WITH    q (user_initial, user_from, user_to, link) AS
        (
        SELECT  user_id, user_id, user_id, link
        FROM    users
        UNION ALL
        SELECT  user_initial, q.user_to, ud.user_to, link + 1
        FROM    q
        JOIN    user_delegates ud
        ON      ud.user_from = q.user_to
        )
SELECT  *
FROM    (
        SELECT  *, ROW_NUMBER() OVER (PARTITION BY user_initial ORDER BY link DESC) rn
        FROM    q
        )
WHERE   rn = 1