如何在递归表 (SQL) 中获取 2 行的最低公共父级 [英] How to get lowest, common parent for 2 rows in recursive table (SQL)

问题描述

假设我们有一个具有经典manager id"递归关系的表:

Let's say that we have we have a table with the classic 'manager id' recursive relationship:

用户user_id 整数manager_id int(指user_id)

Users user_id int manager_id int (refers to user_id)

如果您随机选择表中的 2 行 - 或 2 个节点 - 您如何找到最低级别的共同祖先?我的平台是 SQL Server 2005 (Transact-SQL)，但任何符合 ANSI 的 SQL 也可以使用...

If you randomly select 2 rows in the table- or 2 nodes- how do you find the lowest level, common ancestor? My platform is SQL Server 2005 (Transact-SQL) but any ANSI compliant SQL will also work...

推荐答案

对 Quassnoi 的回答进行了一些小的编辑，它有效:

A few minor edits to Quassnoi's answer, and it works:

WITH
    hier1 (id, parent) AS (
    SELECT      id, parent
    FROM        table
    WHERE       id = @user1
    UNION ALL
    SELECT      id, parent
    FROM        table l, hier1 h
    WHERE       l.id = h.parent
    ),
    hier2 (id, parent) AS (
    SELECT      id, parent
    FROM        table
    WHERE       id = @user2
    UNION ALL
    SELECT      id, parent
    FROM        table l, hier1 h
    WHERE       l.id = h.parent
    )
SELECT  TOP 1 hier1.id
FROM    hier1, hier2
WHERE   hier1.id = hier2.id

这篇关于如何在递归表 (SQL) 中获取 2 行的最低公共父级的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！