以下家庭：
这个构建架构是：
create table PersonConn (child int,parent int) insert into PersonConn values (1,2) insert into PersonConn values (1,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
它将占用所选成员的所有祖先(在此示例中为ID 1),但不是兄弟,例如.查询仅在族树中上升.
我的问题是：
How to get all members of a family (sons,parents,grandfathers,
uncles,cousins,etc…) starting from a single person?
UPDATE
解决此问题的一种方法是将人员插入临时表中.在您将PersonConn表与此临时表连接并插入其他人之后.这样做直到不再插入任何人.我正在寻找一种更有效(和优雅)的方式.我在PersonConn表中有大约200MM的记录.
解决方法
我发现的解决方案并不好.它提供了正确的答案,但速度很慢,即使对于这个非常小的桌子也是如此.
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
