sql - 家谱的递归 CTE 不递归

Question

我正在尝试用我有限的 SQL 技能来构建递归 CTE 查询。
我有两个模拟家谱的表:

CREATE TABLE "Relation" (
  "id" integer not null primary key autoincrement,
  "person1Id" integer,
  "person2Id" integer,
  foreign key("person1Id") references "Person"("id"),
  foreign key("person2Id") references "Person"("id"));

CREATE TABLE "Person" (
  "id" integer not null primary key autoincrement,
  "name" varchar(255),
  "gender" varchar(255),
  "bornToId" integer,
  foreign key("bornToId") references "Relation"("id"));

我试图制作一个 sqlfiddle，但我不断收到“糟糕，出了点问题”，所以我将插入内容粘贴到此处:

  INSERT INTO `Person` (id,name,gender,bornToId) VALUES
 (1,'William I','M',NULL),
 (2,'Matilde of Flanders','F',NULL),
 (3,'William Rufus','M',1),
 (4,'Henry I','M',1),
 (5,'Matilde of Scotland','F',NULL),
 (6,'William Adelin','M',2),
 (7,'Matilde Holy Roman Empress','F',2),
 (8,'Geoffrey of Anjou','M',NULL),
 (9,'Henry II','M',3),
 (10,'Eleanor of Aquitane','F',NULL),
 (11,'Richard I','M',4),
 (12,'John','M',4),
 (13,'Robert Curthose','M',1),
 (14,'Adeliza','F',NULL),
 (15,'Adela of Normandy','F',1),
 (16,'Stephen II Henry','M',NULL),
 (17,'Stephen of Blois','M',6);

INSERT INTO `Relation` (id,person1Id,person2Id) VALUES (1,1,2),
 (2,4,5),
 (3,7,8),
 (4,9,10),
 (5,4,14),
 (6,15,16);

我首先将问题分解为子查询。这些都按我的预期工作(sqlite 3.14.0，DB Browser，在 Mac 上):

找一个有 0 个、1 个或多个关系的人(合作伙伴)

select
  pers.id as id,
  pers.name as name,
  partner.id as partnerId,
  partner.name as partnerName
from Person pers
join Relation
  on pers.id = Relation.person1Id
  or pers.id  = Relation.person2Id
join Person partner
  on (partner.id = Relation.person1Id and
     partner.id <> pers.id)
  or (partner.id = Relation.person2Id and
     partner.id <> pers.id)
where pers.id = 4

寻找一个人的 parent (如果有的话)

select 
  parent1.id as parent1Id,
  parent1.name as parent1Name,
  parent2.id as parent2Id,
  parent2.name as parent2Name,
  parents.id as parentsId
from Person pers
left join Relation parents on pers.bornToId = parents.id
left join Person parent1 on parents.person1Id = parent1.id
left join Person parent2 on parents.person2Id = parent2.id
where pers.id = 4

将上述两者结合起来也可以，但我将在此处省略该查询。

找到属于关系的 child

select pers.id, pers.name
from Person pers
where pers.bornToId = 1

现在，将所有这些放在递归 cte 查询中给我带来了问题。到目前为止，我的尝试导致了这个怪物的查询，但它只返回一条记录(“1”“William I”“2”“Matilde of Flanders”“NULL”“NULL”“NULL”“NULL”)，所以显然它不进入递归。

WITH FamilyTree (
  id,
  name,
  partnerId,
  partnerName,
  parent1Id,
  parent1Name,
  parent2Id,
  parent2Name,
  parentsId
)
AS (
select
  pers.id as id,
  pers.name as name,
  partner.id as partnerId,
  partner.name as partnerName,
  parent1.id as parent1Id,
  parent1.name as parent1Name,
  parent2.id as parent2Id,
  parent2.name as parent2Name,
  parents.id as parentsId
from Person pers
left join Relation
    on pers.id = Relation.person1Id
    or pers.id  = Relation.person2Id
left join Person partner
    on (partner.id = Relation.person1Id and
       partner.id <> pers.id)
    or (partner.id = Relation.person2Id and
       partner.id <> pers.id)
left join Relation parents on pers.bornToId = parents.id
left join Person parent1 on parents.person1Id = parent1.id
left join Person parent2 on parents.person2Id = parent2.id
where pers.id = 1
union all


select
  pers.id as id,
  pers.name as name,
  partner.id as partnerId,
  partner.name as partnerName,
  parent1.id as parent1Id,
  parent1.name as parent1Name,
  parent2.id as parent2Id,
  parent2.name as parent2Name,
  parents.id as parentsId
from Person pers
left join Relation
    on pers.id = Relation.person1Id
    or pers.id  = Relation.person2Id
left join Person partner
    on (partner.id = Relation.person1Id and
       partner.id <> pers.id)
    or (partner.id = Relation.person2Id and
       partner.id <> pers.id)
left join Relation parents on pers.bornToId = parents.id
left join Person parent1 on parents.person1Id = parent1.id
left join Person parent2 on parents.person2Id = parent2.id

JOIN FamilyTree AS fam
     ON pers.bornToId = fam.parentsId
)


select
  id,
  name,
  partnerId,
  partnerName,
  parent1Id,
  parent1Name,
  parent2Id,
  parent2Name
from FamilyTree

我为此花了很多时间，所以我认为是时候请教专家了。

如果您想知道，最终目标是拥有嵌套的 javascript 对象，例如:

{
  ...
  relations: [{
    ...
    children: [{

所以我可以在 D3 树中使用它。

Answer 1

酷，我想我明白了。线索就在它确实返回的那张唱片中，我忽略了它。该记录没有 parentId，因为它是根人员。但在查询中，我将下一个 Person 的“bornToId”与值为 NULL 的 parentId 匹配。

我应该与我没有在选择中定义的根人的关系的 id 匹配。

在这个修改后的 cte 查询中，它确实进入了递归，结果看起来不错。我仍然需要做一些检查，但无论如何我都会发布这个，以免其他人发现这个特定的错误。

WITH FamilyTree (
  id,
  name,
  partnerId,
  partnerName,
  parent1Id,
  parent1Name,
  parent2Id,
  parent2Name,
  parentsId,
  relationId
)
AS (
select
  pers.id as id,
  pers.name as name,
  partner.id as partnerId,
  partner.name as partnerName,
  parent1.id as parent1Id,
  parent1.name as parent1Name,
  parent2.id as parent2Id,
  parent2.name as parent2Name,
  parents.id as parentsId,
  rel.id as relationId
from Person pers
left join Relation rel
    on pers.id = rel.person1Id
    or pers.id  = rel.person2Id
left join Person partner
    on (partner.id = rel.person1Id and
       partner.id <> pers.id)
    or (partner.id = rel.person2Id and
       partner.id <> pers.id)
left join Relation parents on pers.bornToId = parents.id
left join Person parent1 on parents.person1Id = parent1.id
left join Person parent2 on parents.person2Id = parent2.id
where pers.id = 1
union all


select
  pers.id as id,
  pers.name as name,
  partner.id as partnerId,
  partner.name as partnerName,
  parent1.id as parent1Id,
  parent1.name as parent1Name,
  parent2.id as parent2Id,
  parent2.name as parent2Name,
  parents.id as parentsId,
  rel.id as relationId
from Person pers
left join Relation rel
    on pers.id = rel.person1Id
    or pers.id  = rel.person2Id
left join Person partner
    on (partner.id = rel.person1Id and
       partner.id <> pers.id)
    or (partner.id = rel.person2Id and
       partner.id <> pers.id)
left join Relation parents on pers.bornToId = parents.id
left join Person parent1 on parents.person1Id = parent1.id
left join Person parent2 on parents.person2Id = parent2.id

JOIN FamilyTree AS fam
     ON pers.bornToId = fam.relationId
)


select
  id,
  name,
  partnerId,
  partnerName,
  parent1Id,
  parent1Name,
  parent2Id,
  parent2Name
from FamilyTree