sql - 获取家庭成员

Question

假设以下家庭:

其构建架构是:

create table PersonConn (child int, parent int)
insert into PersonConn values (1,2)
insert into PersonConn values (1,3)
insert into PersonConn values (5,3)
insert into PersonConn values (5,4)
insert into PersonConn values (6,7)
insert into PersonConn values (6,8)
insert into PersonConn values (2,9)
insert into PersonConn values (2,10)
insert into PersonConn values (3,11)
insert into PersonConn values (3,12)

为了获取家庭成员的祖先，我可以使用递归，如下所示:

WITH Childs AS (
    SELECT distinct Child, Parent
    FROM  PersonConn
    WHERE Child = 1
    UNION ALL
    SELECT t2.Child, t2.Parent
    FROM   [Childs] t1
    INNER JOIN  PersonConn t2
        ON  t2.Child = t1.parent
)
SELECT PARENT FROM CHILDS

SQL Fiddle

它将采用所选成员的所有祖先(在本例中为 ID 1)，但不包括兄弟。该查询仅在家谱中出现。

我的问题是:

How to get all members of a family (sons, parents, grandfathers, uncles, cousins, etc...) starting from a single person?

更新

解决此问题的一种方法是使用循环将人员插入临时表中。您可以将 PersonConn 表与此临时表连接起来并插入其他人。这样做直到不再有人插入为止。我正在寻找一种更有效(且优雅)的方式。 我在 PersonConn 表中有大约 200MM 记录。

Answer 1

我找到的解决方案一点都不好。它给出了正确的答案，但速度非常慢，即使对于这个非常小的表也是如此。

 DECLARE @INCLUIDOS TABLE (ID INT)

 INSERT INTO @INCLUIDOS VALUES(1)

 DECLARE @PAST_QUANT INT = 0
 DECLARE @QUANT INT = 1 

 WHILE @QUANT <> @PAST_QUANT
 BEGIN

     SET @PAST_QUANT = @QUANT

     INSERT INTO @INCLUIDOS
        SELECT PARENT 
        FROM PERSONCONN 
        WHERE CHILD IN (SELECT ID FROM @INCLUIDOS)
            AND PARENT NOT IN (SELECT ID FROM @INCLUIDOS)

    INSERT INTO @INCLUIDOS
        SELECT CHILD
        FROM PERSONCONN
        WHERE PARENT IN (SELECT ID FROM @INCLUIDOS)
            AND CHILD NOT IN (SELECT ID FROM @INCLUIDOS)

    SET @QUANT = (SELECT COUNT(*) FROM @INCLUIDOS)

END

SELECT DISTINCT ID FROM @INCLUIDOS

SQL Fiddle