postgresql 如何递归查询一个树形结构的多个表？

nkoocmlb 于 2022-11-29 发布在 PostgreSQL

关注(0)|答案(1)|浏览(248)

这是我的ER图。如果我知道user_id，我如何使用一个SQL查询得到一个用户的用户数据=〉projects=〉boards=〉post？
我已经了解了递归CTE，但是我能找到的所有示例都将所有数据存储到一个表中。我将数据拆分到4个表中。是否有方法在此处获取所有用户数据？
我没有任何SQL来显示我尝试过，因为老实说，我甚至不知道从哪里开始。我想只是添加一个user_id字段到每个表，但这似乎不是正确的解决方案。
编辑：这是处理连接中冗余数据的好方法吗？
我有三个想法：
1-〉获取所有重复的数据。数据库中没有重复的数据和一个查询，但发送的数据比我需要的要多。
2-〉为每个表将查询拆分为四个单独的查询。没有重复的数据，但我正在运行四个单独的查询。
3-〉将“user_id”添加到每个表中，并直接查询每个表，这样我的数据库中就有冗余数据了。
4-〉？？更好的选项？还是更简洁的查询？
下面是我对拆分查询的想法（2）

-- user data
SELECT nickname, theme FROM users WHERE user_id = 'exampleid';

-- project data
SELECT
    pr.project_id,
    pr.time_created ,
    pr.time_last_modified,
    pr.title 
FROM users u
    INNER JOIN projects pr
        ON u.user_id = pr.fk_projects_users 
WHERE u.user_id = 'exampleid';

-- board data
SELECT
    b.board_id,
    b.fk_boards_projects,
    b.title,
    b.order_position,
    b.color
FROM users u
    INNER JOIN projects pr
        ON u.user_id = pr.fk_projects_users 
    INNER JOIN boards b
        ON pr.project_id = b.fk_boards_projects
WHERE u.user_id = 'exampleid';

-- post data
SELECT
    po.post_id,
    po.fk_posts_boards,
    po.time_created,
    po.title,
    po.priority,
    po.time_due,
    po.body
FROM users u
    INNER JOIN projects pr
        ON u.user_id = pr.fk_projects_users 
    INNER JOIN boards b
        ON pr.project_id = b.fk_boards_projects
    INNER JOIN posts po
        ON po.post_id = b.board_id
WHERE u.user_id = 'exampleid'

postgresql

来源：https://stackoverflow.com/questions/74590940/how-to-query-a-tree-like-structure-with-multiple-tables-recursively

1条答案

按热度按时间

kmbjn2e31#

一系列简单的joins就足够了。
一次访问多个表（或同一表的多个示例）的查询称为联接查询。它们将一个表中的行与另一个表中的行组合在一起，并使用一个表达式指定要配对的行
Demo:

create table users   (user_id int);
create table projects(project_id int, fk_projects_users int);
create table boards  (board_id int,   fk_boards_projects int);
create table posts   (post_id int,    fk_posts_boards int);

insert into users    values (1),       (2),        (3);
insert into projects values (11,1),    (12,1),     (13,2);
insert into boards   values (101,11),  (102,11),   (103,13);
insert into posts    values (1001,101),(1002,101), (1003,102),(1004,103);

select  po.post_id 
from    users u 
    inner join projects pr 
        on u.user_id=pr.fk_projects_users 
    inner join boards b 
        on pr.project_id=b.fk_boards_projects 
    inner join posts po 
        on b.board_id=po.fk_posts_boards
where u.user_id=1;

-- post_id
-----------
--    1001
--    1002
--    1003
--(3 rows)

下一个合乎逻辑的步骤是开始使用aggregate functions来收集每个用户的统计信息。

select  
  u.user_id,
  array_agg(po.post_id) as "array of all their post ids",
  count(po.post_id)     as "how many posts this user has",
  max(po.post_id)       as "latest post of this user (by id)"
from    users u 
    inner join projects pr 
        on u.user_id=pr.fk_projects_users 
    inner join boards b 
        on pr.project_id=b.fk_boards_projects 
    inner join posts po 
        on b.board_id=po.fk_posts_boards
group by u.user_id;

-- user_id | array of all their post ids | how many posts this user has | latest post of this user (by id)
-----------+-----------------------------+------------------------------+----------------------------------
--       2 | {1004}                      |                            1 |                             1004
--       1 | {1001,1002,1003}            |                            3 |                             1003
--(2 rows)

赞(0）回复(0）举报 2022-11-29

我来回答

postgresql 如何递归查询一个树形结构的多个表？

1条答案

相关问题

热门标签

最新问答