我有一个具有同级排序的树层次结构。我需要添加对其他树的引用。

这是数据:

drop table if exists org; CREATE TABLE org(id int primary key, name text, boss int, sibling int, ref int) without rowid;
INSERT INTO org VALUES(0, 'Alice', NULL, null, null);
INSERT INTO org VALUES(1, 'Bob', 0, null, null);
INSERT INTO org VALUES(2, 'Cindy', 0, 1, null);
INSERT INTO org VALUES(3, 'Dave', 1, 4, 7);
INSERT INTO org VALUES(4, 'Emma', 1, null, null);
INSERT INTO org VALUES(5, 'Fred', 2, null, null);
INSERT INTO org VALUES(6, 'Gail', 2, 5, null);
INSERT INTO org VALUES(7, 'Helen', NULL, null, null);
INSERT INTO org VALUES(8, 'Igor', 7, null, null);
INSERT INTO org VALUES(9, 'Jerome', 7, 8, null);

戴夫(Dave)引用了海伦(Helen)领导的那棵树。

我添加了refs子句:
WITH RECURSIVE
refs(id, name, boss, sibling, ref, lref) AS (
 SELECT id, name, boss, sibling, ref, 0 FROM org
UNION ALL
 SELECT org.id, org.name, org.boss, org.sibling, org.ref, refs.lref+1
 FROM org JOIN refs ON org.id=refs.ref
),
sibs(id, name, boss, lref, lsib) AS (
 SELECT id, name, boss, lref, 0 FROM refs
 WHERE sibling IS NULL
UNION ALL
 SELECT refs.id, refs.name, refs.boss, refs.lref, sibs.lsib + 1
 FROM refs
 JOIN sibs ON refs.boss = sibs.boss
 AND refs.sibling = sibs.id
),
tree(id, name, lsib, lref, level) AS (
 select id, name, 0, 0, 0 from org where id = 0
UNION ALL
 SELECT sibs.id, sibs.name, sibs.lsib, sibs.lref, tree.level+1
 FROM sibs JOIN tree ON sibs.boss=tree.id
ORDER BY 4 DESC, 5 DESC, 3 DESC
)
SELECT group_concat(name) FROM tree;

但是结果不包括海伦的树:



我怎样才能得到海伦的树的完整结果:



编辑:

鲍勃(Bob)和辛迪(Cindy)以及弗雷德&盖尔(Fred&Gail)被颠倒了……实际预期结果是:

最佳答案

我认为您无法获得以下预期输出:

因为您要针对同一案例寻求不同的结果:

  • Cindy和Bob:同一个 parent 。辛迪(Cindy)和鲍勃(Bob)同胞,而鲍勃(Bob)之前你要辛迪(Cindy)。
  • Jerome和Igor:同一个 parent 。杰罗姆(Jerome)与伊戈尔(Igor)同胞,而你想要伊戈尔(Igor)在杰罗姆(Jerome)之前。

  • (如果必须在Bob之前打印Cindy,那么必须在Igor之前打印Jerome)
    我认为预期的输出应为:

    或者


    第一种方法
    我尝试了以下查询(使用refs.id而不是org.boss):
    WITH RECURSIVE
    refs(id, name, boss, sibling, ref, lref) AS (
     SELECT id, name, boss, sibling, ref, 0 FROM org
    
    UNION ALL
     SELECT org.id, org.name, refs.id, org.sibling, org.ref, refs.lref+1
     FROM org JOIN refs ON org.id=refs.ref
    ),
    sibs(id, name, boss, lref, lsib,ref) AS (
     SELECT id, name, boss, refs.lref, 0,ref FROM refs
      WHERE sibling IS NULL
    UNION ALL
     SELECT refs.id, refs.name, refs.boss, refs.lref, sibs.lsib + 1,refs.ref
     FROM refs
     JOIN sibs ON refs.boss = sibs.boss
     AND refs.sibling = sibs.id
    ),
    tree(id, name, lsib, lref, level) AS (
     select org.id, org.name, 0, 0, 0 from org
      where id = 0
    
    UNION ALL
     SELECT sibs.id, sibs.name, sibs.lsib, sibs.lref, tree.level+1
     FROM sibs JOIN tree ON sibs.boss = tree.id
    ORDER BY  4 DESC, 5 DESC
    )
    select group_concat(name) from tree;
    

    第二种方法
    我使用了另一种方法:
    WITH RECURSIVE
    pc(id,name,parent,priority) AS(
      select org.id,org.name,coalesce(refs.id,org.boss,-1) as "parent",
      case
      when refs.id is not null then 3
      when sibls.id is not null then 2
      when org.boss is not null then 1
      else 0 end as  "priority"
      from org left join org as refs on org.id = refs.ref
      left join org as sibls on org.id = sibls.sibling
    where org.id > 0),
      tree(id,name,parent,priority,level) AS(
        select id,name,0,0,0 from org where id = 0
        UNION ALL
        select pc.id,pc.name,pc.parent,pc.priority,tree.level + 1 from pc
        join tree on tree.id = pc.parent
        order by 3 desc )
        select group_concat(name) from tree
    

    关于sql - 有子句: nesting trees,我们在Stack Overflow上找到一个类似的问题:https://stackoverflow.com/questions/55549902/

    10-13 08:54