获取家庭成员 [英] Get family members

问题描述

假设以下家庭:

这个构建架构是:

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)

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

To get the ancestors of a family member I can use recursion as showed bellow:

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 小提琴

它将获取所选成员的所有祖先(在此示例中为 ID 1)，但不包括兄弟.查询只出现在家庭树中.

It will take all the ancestors of selected member (ID 1 in this example), but not the brothers for example. The query goes up only in family tree.

我的问题是:

如何让所有家庭成员(儿子、父母、祖父、叔叔、堂兄弟等……)从一个人开始?

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

更新

解决这个问题的一种方法是在临时表中插入一个人的循环.在您可以将 PersonConn 表与此临时表连接并插入其他人之后.这样做直到没有人再插入.我正在寻找一种更有效(和优雅)的方式.我在 PersonConn 表中有大约 200MM 的记录.

One method to solve this is a loop that inserts a person in a temporary table. After you could join PersonConn table with this temporary table and inserts other people. Do this until no one is inserted anymore. I am looking for a more efficient (and elegant) way. I have about 200MM records in PersonConn table.

推荐答案

首先我建议你使用 hierarchyid 表的列.

In first I suggest you that use hierarchyid column for your table.

尝试以下查询(没有hierarchyid):

Try following query (without hierarchyid):

DECLARE @PersonId INT = 3

;WITH Parents AS (
    SELECT @PersonId AS Id
    UNION ALL
    SELECT child
    FROM PersonConn pc
    INNER JOIN Parents p ON pc.parent = p.Id
    ),
    Childs AS (
    SELECT distinct pc.Child, pc.Parent
    FROM  PersonConn pc
    INNER JOIN Parents p ON pc.child = p.Id OR pc.parent = p.Id
    UNION ALL
    SELECT t2.Child, t2.Parent
    FROM   [Childs] t1
    INNER JOIN  PersonConn t2
        ON  t2.Child = t1.parent
)
SELECT DISTINCT CASE WHEN N.n=1 THEN parent ELSE child END 
FROM CHILDS
CROSS APPLY(SELECT 1 UNION SELECT 2)N(n)

SQL 小提琴

这篇关于获取家庭成员的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！