Цикл SQL над родословной

Question

Использование 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]

Answer 1

Я бы посоветовал вам взглянуть на рекурсию, используя CTE (Common Table Expression).

Это позволит вам рекурсивно просматривать родителей, пока не будет найден общий предок, сохраняя при этом ценность для этого.

Answer 2

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

Answer 3

На это нельзя ответить в реалистической моде - игнорируйте часть SQL на мгновение, но вы даже не знаете, чего хотите. "возможно" - подумайте еще раз. Что делать, если у вас есть несколько частичных предков? Что вы делаете тогда?

Найти всех предков данного потомства тривиально (временная таблица, рекурсивно заполнить ее родителями, добавив в качестве поля слово "вылет").

Затем вы можете объединить две временные таблицы. Пока неплохо (и извините, так и должно быть, потому что ваш иерарх может вернуться на много поколений назад).

Но оттуда вы все равно должны найти действительно разумный алгоритм того, что это должно означать - в нетривиальных сценариях;)