代码之家 › 专栏 › 技术社区 › simon831

在族树上进行SQL循环

family-tree tsql sql

simon831 · 技术社区 · 15 年前

使用SQL Server 2008。我在一张桌子上有一个动物的家谱,我想给一些关于后代“遗传多样性”(或不多样性)的信息。在SQL中,如何生成合理的度量来显示父级之间的关系有多密切? 也许是某种比例的共享血液,或者是在有一个共享祖先之前的几代人?

AnimalTable 
Id
Name
mumId
dadId

select * from AnimalTable child
inner join AnimalTable mum on child.[mumId] = mum.[Id]
inner join AnimalTable dad on child.[dadId] = dad.[Id]

inner join AnimalTable mums_mum on mum.[mumId] = mums_mum.[Id]
inner join AnimalTable mums_dad on mum.[dadId] = mums_dad.[Id]

inner join AnimalTable dads_mum on dad.[mumId] = dads_mum.[Id]
inner join AnimalTable dads_dad on dad.[dadId] = dads_dad.[Id]

3 回复 | 直到 13 年前

Robin Day 15 年前

我建议您使用CTE(公共表表达式)查看递归。

这将允许您递归地查看父级,直到找到一个共同的父级,同时保持此值。

Quassnoi 15 年前

WITH    hier1(parent, level) AS
        (
        SELECT  mum, 1
        FROM    AnimalTable a
        WHERE   a.id = @first_animal
        UNION ALL
        SELECT  dad, 1
        FROM    AnimalTable a
        WHERE   a.id = @first_animal
        UNION ALL
        SELECT  mum, level + 1
        FROM    q
        JOIN    AnimalTable a
        ON      a.id = q.parent
        UNION ALL
        SELECT  dad, level + 1
        FROM    q
        JOIN    AnimalTable a
        ON      a.id = q.parent
        ),
        hier2(parent, level) AS
        (
        SELECT  mum, level
        FROM    AnimalTable a
        WHERE   a.id = @second_animal
        UNION ALL
        SELECT  dad, level
        FROM    AnimalTable a
        WHERE   a.id = @second_animal
        UNION ALL
        SELECT  mum, level + 1
        FROM    q
        JOIN    AnimalTable a
        ON      a.id = q.parent
        UNION ALL
        SELECT  dad, level + 1
        FROM    q
        JOIN    AnimalTable a
        ON      a.id = q.parent
        )
SELECT  TOP 1
        h1.parent,
        CASE WHEN h1.level < h2.level THEN h1.level ELSE h2.level END AS minlevel
FROM    hier1 h1
JOIN    hier2 h2
ON      h1.parent = h2.parent
ORDER BY
        2

TomTom 15 年前

这不能用实际的方式来回答——暂时忽略SQL部分,但您甚至不知道自己想要什么。”也许“—好吧,再仔细想想。如果你有多个部分祖先呢?那你怎么办?

查找给定子代的所有祖先是很简单的(临时表,递归地填充父代的祖先表,将“generation away”添加为字段)。

然后您可以联接这两个临时表。到目前为止还不错(很抱歉,这基本上是必须的,因为你的等级制度可以追溯到很多代以前)。

但是从那里你仍然需要找到一个合理的算法来解释这意味着什么——在非琐碎的场景中;)