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

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

  •  2
  • 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 年前
        1
  •  1
  •   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 || '.%'
    );
    
        2
  •  0
  •   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  );
    
        3
  •  0
  •   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);