PostgreSQL:如何在线性“祖先后代”中找到最后一个后代。关系 [英] PostgreSQL: How to find the last descendant in a linear "ancestor-descendant" relationship
本文介绍了PostgreSQL:如何在线性“祖先后代”中找到最后一个后代。关系的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我具有以下数据库结构:
I have the following DB structure:
RELATIONSHIP_TABLE
- id << primary key
- id_ancestor << foreign key to the same table
- id_entry << foreign key to "ENTRY_TABLE"
ENTRY_TABLE
- id
- name
...
表 RELATIONSHIP_TABLE
中的层次结构是线性的。这意味着一条记录最多可以是另一条记录的祖先。示例:
The hierarchy in table "RELATIONSHIP_TABLE
" is linear. That means a record can be at most ancestor of one other record. Examples:
1. record1
2. record2 <- record3 <- record4
3. record5 <- record7 <- record9 <- record12
特定层次结构中的每个记录都具有相同的 id_entry
。现在,我想找到带有特定 id_entry
的最后一个后代。以下示例的结果将是:
Every record within a particular hierarchy has the same "id_entry
". Now, I would like to find the last descendant with a specific "id_entry
". The result of the examples below would be:
1. record1
2. record4
3. record12
有人知道解决方案吗?
Does anyone know a solution?
预先感谢:)
QStormDS
推荐答案
SELECT *
FROM relationship_table rt
WHERE rt.id_entry = 42
AND NOT EXISTS (
SELECT * FROM relationship_table nx
WHERE nx.id_entry = 42 -- you can possibly omit this clause
AND nx.id_ancestor = rt.id -- No children poining to rt ...
)
;
这篇关于PostgreSQL:如何在线性“祖先后代”中找到最后一个后代。关系的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文