代码之家 › 专栏 › 技术社区 › Eugen Konkov

如何将查询更改为仅保留叶节点

postgresql

Eugen Konkov · 技术社区 · 6 年前

我有一个包含以下数据的表格:

 id | parent_id |   short_name   
----+-----------+----------------
  6 |         5 | cpu
  7 |         5 | ram
 14 |         9 | tier-a
 15 |         9 | rfc1918
 16 |         9 | tolerant
 17 |         9 | nononymous
 13 |        12 | cloudstack
  5 |        13 | virtualmachine
  8 |        13 | volume
  9 |        13 | ipv4
  3 |           | domain
  4 |           | account
 12 |           | vdc
(13 rows)

对于递归查询,它如下所示:

with recursive tree ( id, parent_id, short_name, deep_name ) as (
select resource_type_id, parent_resource_type_id, short_name, short_name::text 
from resource_type
where parent_resource_type_id is null
union all
select rt.resource_type_id as id, rt.parent_resource_type_id, rt.short_name,
 tree.deep_name || '.' || rt.short_name
from tree, resource_type rt
where tree.id = rt.parent_resource_type_id
)
select * from tree;

 id | parent_id |   short_name   |             deep_name             
----+-----------+----------------+-----------------------------------
  4 |           | account        | account
  3 |           | domain         | domain
 12 |           | vdc            | vdc
 13 |        12 | cloudstack     | vdc.cloudstack
  9 |        13 | ipv4           | vdc.cloudstack.ipv4
  5 |        13 | virtualmachine | vdc.cloudstack.virtualmachine
  8 |        13 | volume         | vdc.cloudstack.volume
  6 |         5 | cpu            | vdc.cloudstack.virtualmachine.cpu
 15 |         9 | rfc1918        | vdc.cloudstack.ipv4.rfc1918
 17 |         9 | nononymous     | vdc.cloudstack.ipv4.nononymous
 16 |         9 | tolerant       | vdc.cloudstack.ipv4.tolerant
 14 |         9 | tier-a         | vdc.cloudstack.ipv4.tier-a
  7 |         5 | ram            | vdc.cloudstack.virtualmachine.ram
(13 rows)

如何修复查询,使结果只得到叶子? 如。 vdc.cloudstack.volume 争吵不休 vdc , vdc.cloudstack 排

UPD
没有孩子的争吵

3 回复 | 直到 6 年前

Laurenz Albe 6 年前

排除其中的行 deep_name 在桌子的其他地方有一个超弦:

WITH RECURSIVE tree AS (...)
SELECT * FROM tree AS t1
WHERE NOT EXISTS (
   SELECT 1 FROM tree AS t2
   WHERE t2.deep_name
         LIKE t1.deep_name || '.%'
);

Eugen Konkov 6 年前

Laurenz Albe的回答给了我一个想法。我认为数数孩子比用字符串更有效率。

我的解决方案是:

WITH RECURSIVE tree AS (...)
SELECT * FROM tree t1 
WHERE not EXISTS ( SELECT 1 FROM tree t2 WHERE t1.id = t2.parent_id  );

enjayaitch 6 年前

叶节点本身不是父节点的子节点。

如果您只需要一个叶注释列表,而不需要递归CTE,那么您只需要使用首选格式的反连接。

如果(就像我想象的那样)需要deep_名称,我会将递归CTE的结果反连接到id=parent_id上的原始源表。

WITH RECURSIVE tree AS (...)
SELECT * FROM tree AS t1
WHERE NOT EXISTS (SELECT 1 FROM resource_type AS t2
                  WHERE t2.parent_resource_type_id = t1.id);