PostgreSQL递归父/子查询 [英] PostgreSQL recursive parent/child query

查看:148
本文介绍了PostgreSQL递归父/子查询的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

在为递归查询编写PostgreSQL文档时遇到了一些麻烦,想知道是否有人可以为以下内容提供建议.

I'm having some trouble working out the PostgreSQL documentation for recursive queries, and wonder if anyone might be able to offer a suggestion for the following.

以下是数据:

                                            Table "public.subjects"
      Column       |            Type             | Collation | Nullable |               Default                
-------------------+-----------------------------+-----------+----------+--------------------------------------
 id                | bigint                      |           | not null | nextval('subjects_id_seq'::regclass)
 name              | character varying           |           |          | 



                                        Table "public.subject_associations"
   Column   |            Type             | Collation | Nullable |                     Default                      
------------+-----------------------------+-----------+----------+--------------------------------------------------
 id         | bigint                      |           | not null | nextval('subject_associations_id_seq'::regclass)
 parent_id  | integer                     |           |          | 
 child_id   | integer                     |           |          | 

在这里,一个受试者"可能有很多父母和很多孩子.当然,在顶层,一个主题没有父母,在底层,没有孩子.例如:

Here, a "subject" may have many parents and many children. Of course, at the top level a subject has no parents and at the bottom no children. For example:

 parent_id  |  child_id  
------------+------------
     2      |     3
     1      |     4
     1      |     3
     4      |     8
     4      |     5
     5      |     6
     6      |     7

我要寻找的是从child_id开始以获得所有祖先,并以parent_id获得所有后代.因此:

What I'm looking for is starting with a child_id to get all the ancestors, and with a parent_id, all the descendants. Therefore:

parent_id 1 -> children 3, 4, 5, 6, 7, 8
parent_id 2 -> children 3

child_id 3 -> parents 1, 2
child_id 4 -> parents 1
child_id 7 -> parents 6, 5, 4, 1

尽管我有很多类似事情的例子,但我很难理解它们,因此欢迎我提出任何建议.

Though there seem to be a lot of examples of similar things about I'm having trouble making sense of them, so any suggestions I can try out would be welcome.

推荐答案

要获得主题1的所有孩子,可以使用

To get all children for subject 1, you can use

WITH RECURSIVE c AS (
   SELECT 1 AS id
   UNION ALL
   SELECT sa.child_id
   FROM subject_associations AS sa
      JOIN c ON c.id = sa. parent_id
)
SELECT id FROM c;

这篇关于PostgreSQL递归父/子查询的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!

查看全文
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆