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

Question

我有以下数据的表：

 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.volumerow 和 no vdc,vdc.cloudstack行

没有子级的UPD
行

标签： postgresql

Answer 1

排除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 || '.%'
);