postgresql - 如何创建将父 ID 和祖父母 ID 推送到数组中的递归 cte 查询
问题描述
我有一个我正在尝试创建的 postgresql 表。这是我的 cte,我在这里插入值
BEGIN;
CREATE TABLE section (
id SERIAL PRIMARY KEY,
parent_id INTEGER REFERENCES section(id) DEFERRABLE,
name TEXT NOT NULL UNIQUE );
SET CONSTRAINTS ALL DEFERRED;
INSERT INTO section VALUES (1, NULL, 'animal');
INSERT INTO section VALUES (2, NULL, 'mineral');
INSERT INTO section VALUES (3, NULL, 'vegetable');
INSERT INTO section VALUES (4, 1, 'dog');
INSERT INTO section VALUES (5, 1, 'cat');
INSERT INTO section VALUES (6, 4, 'doberman');
INSERT INTO section VALUES (7, 4, 'dachshund');
INSERT INTO section VALUES (8, 3, 'carrot');
INSERT INTO section VALUES (9, 3, 'lettuce');
INSERT INTO section VALUES (10, 11, 'paradox1');
INSERT INTO section VALUES (11, 10, 'paradox2');
SELECT setval('section_id_seq', (select max(id) from section));
WITH RECURSIVE last_run(parent_id, id_list, name_list) AS (
???
SELECT id_list, name_list
FROM last_run ???
WHERE ORDER BY id_list;
ROLLBACK;
我知道递归查询是最好的方法,但不确定如何实现它。到底是什么?我想要得到的是下表:
id_list | name_list
---------+------------------------
{1} | animal
{2} | mineral
{3} | vegetable
{4,1} | dog, animal
{5,1} | cat, animal
{6,4,1} | doberman, dog, animal
{7,4,1} | dachshund, dog, animal
{8,3} | carrot, vegetable
{9,3} | lettuce, vegetable
{10,11} | paradox1, paradox2
{11,10} | paradox2, paradox1
解决方案
您可以在单个查询中使用多个递归 CTE:一个用于有效树,另一个用于悖论:
with recursive
cte as (
select *, array[id] as ids, array[name] as names
from section
where parent_id is null
union all
select s.*, s.id||c.ids, s.name||c.names
from section as s join cte as c on (s.parent_id = c.id)),
paradoxes as (
select *, array[id] as ids, array[name] as names
from section
where id not in (select id from cte)
union all
select s.*, s.id||p.ids, s.name||p.names
from section as s join paradoxes as p on (s.parent_id = p.id)
where s.id <> all(p.ids) -- To break loops
)
select * from cte
union all
select * from paradoxes;
结果:
┌────┬──────────┬────────────┬──────────┬────────── ──────────────┐ │ id │ parent_id │ name │ ids │ names │ ├────┼──────────┼────────────┼──────────┼────────── ──────────────┤ │ 1 │ ░░░░ │ 动物 │ {1} │ {动物} │ │ 2 │ ░░░░ │ 矿物 │ {2} │ {矿物} │ │ 3 │ ░░░░ │ 蔬菜 │ {3} │ {蔬菜} │ │ 4 │ 1 │ 狗 │ {4,1} │ {狗,动物} │ │ 5 │ 1 │ 猫 │ {5,1} │ {猫,动物} │ │ 8 │ 3 │ 胡萝卜 │ {8,3} │ {胡萝卜,蔬菜} │ │ 9 │ 3 │ 生菜 │ {9,3} │ {生菜,蔬菜} │ │ 6 │ 4 │ 杜宾犬 │ {6,4,1} │ {杜宾犬,狗,动物} │ │ 7 │ 4 │ 腊肠犬 │ {7,4,1} │ {腊肠犬,狗,动物} │ │ 10 │ 11 │ paradox1 │ {10} │ {paradox1} │ │ 11 │ 10 │ paradox2 │ {11} │ {paradox2} │ │ 11 │ 10 │ paradox2 │ {11,10} │ {paradox2,paradox1} │ │ 10 │ 11 │ paradox1 │ {10,11} │ {paradox1,paradox2} │ └────┴──────────┴────────────┴──────────┴────────── ──────────────┘
如您所见,结果包括两个不需要的行:{10}, {paradox1}
和{11}, {paradox2}
. 如何过滤掉它们取决于您。
如果您附加另一行,例如,不清楚什么是期望的结果INSERT INTO section VALUES (12, 10, 'paradox3');
。
推荐阅读
- amazon-web-services - 按组名模式获取所有日志流名称
- sql - SQL Left Join with where 子句返回空值
- python - 基于Python中的其他数组元素合并数组元素
- android - Android Fragments:切换beetwen片段并返回上一个状态
- javascript - 如何对具有多个条件的多列求和并显示在一个中
- java - 如何使用 java 将 doc 或 dox 文件转换为图像?用于在浏览器上预览文档
- elasticsearch - Elasticsearch DSL 使用功能评分
- hive - 设置直线 Hive 连接时出现气流错误
- json - 如何在 ionic 3 中使用 for 循环来不迭代对象
- java - 在java中使用Lock on key并发LRU缓存