sql - With 子句:嵌套树
问题描述
我有一个兄弟排序的树层次结构。我需要添加对其他树的引用。
这是数据:
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);
戴夫引用了海伦带领的树。
我添加了 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;
但结果不包括海伦的树:
爱丽丝、辛迪、盖尔、弗雷德、鲍勃、戴夫、艾玛
如何使用 Helen 的树获得完整的结果:
爱丽丝、辛迪、盖尔、弗雷德、鲍勃、戴夫、海伦、伊戈尔、杰罗姆、艾玛
编辑 :
Bob 和 Cindy - 以及 Fred&Gail- 被颠倒了......实际的预期结果是:
爱丽丝、鲍勃、戴夫、海伦、伊戈尔、杰罗姆、艾玛、辛迪、弗雷德、盖尔
解决方案
这将起作用:
SELECT LISTAGG(name, ', ') WITHIN GROUP (ORDER BY name)
FROM org
START WITH boss is null
CONNECT BY PRIOR id= boss
ORDER SIBLINGS BY sibling;
所需输出:
Alice, Bob, Cindy, Dave, Emma, Fred, Gail, Helen, Igor, Jerome
推荐阅读
- python - 我们如何解释随机梯度下降分类器的特征重要性?
- .net - LINQ:如何在 ID 位于我之前查询的列表中的表中查找记录?
- swift - 相机随节点移动后无法按下节点
- android - RecyclerView onClickListener 未触发(Kotlin)
- vue.js - 我似乎无法从网站上删除 # 符号
- php - PHP 创建带休息时间的时隙
- python - Python - 将函数传递给可以修改参数的另一个函数
- c - 从 char 到 int 的奇怪变化,串行端口通信 c
- python - 就 n 和 m 而言,以下算法的运行时复杂度是多少?
- vb.net - 从下拉框中选择项目时尝试激活特定的控制器功能