时间戳

首页 > 解决方案 > 联合和选择

sql - 联合和选择

问题描述

我有一个以下结构的表。p_id 为 null 的元素将显示为根，p_id 列中提到的所有元素显示为内部元素，id 列中的其余元素显示为叶子。这是编写查询的正确方法还是在postgres sql中编写查询的更优化方法。

select id, 'Leaf' as node
from tree
where id NOT IN ( select distinct t1.id as node
                 from tree t1 join tree t2 on t1.id = t2.p_id )
union 
select distinct p_id, 'Inner' as node 
from tree
where p_id NOT IN( select id from tree where p_id IS NULL)
union 
select id, 'Root'
from tree
where p_id is NULL
order by id

相同的表看起来像

我已经尝试了上面的查询，它给出了预期的结果，但是，我不确定这是否完美。

id    p_id
-----------
1   2
3   2
6   8
9   8
2   5
8   5
5   (null)

1   Leaf
2   Inner
3   Leaf
5   Root
6   Leaf
8   Inner
9   Leaf

标签： sqlpostgresql

解决方案

通常在使用树时，会有一个单独的节点表。如果是这样，您不必为此使用union，但可以将逻辑移动到select：

select id,
       (case when not exists (select 1 from tree t where t.id = n.id and t.p_id is null)
             then 'root'
             when not exists (select 1 from tree t where t.p_id = n.id)
             then 'leaf'
             else 'inner'
        end)
from nodes n;

您也可以在没有nodes表格的情况下执行此操作：

select v.id,
       (case when count(*) filter (where t.p_id is null) > 0 then 'root'
             when bool_and(v.is_child) then 'leaf'
             else 'inner'
        end)
from tree t cross join lateral
     (values (id, true), (p_id, false)
     ) v(id, is_child)
where v.id is not null
group by v.id
order by v.id;

这是一个 db<>fiddle。

推荐阅读