代码之家 › 专栏 › 技术社区 › Ulvi

如何在多对多关系中在没有额外重复的情况下联接表

postgresql-13 postgresql

Ulvi · 技术社区 · 4 年前

我有两张带 one-to-many relationship 到父表。我想 join 它们没有额外的重复。

在实际的模式中 一对多关系 s到此父表和子表。我正在分享一个简化的模式,以使问题的根源易于被看到。

任何建议都将不胜感激。

CREATE TABLE computer (
    id SERIAL PRIMARY KEY,
    name TEXT
);

CREATE TABLE c_user (
    id SERIAL PRIMARY KEY,
    computer_id INT REFERENCES computer,
    name TEXT
);

CREATE TABLE c_accessories (
    id SERIAL PRIMARY KEY,
    computer_id INT REFERENCES computer,
    name TEXT
);

INSERT INTO computer (name) VALUES ('HP'), ('Toshiba'), ('Dell');
INSERT INTO c_user (computer_id, name) VALUES (1, 'John'), (1, 'Elton'), (1, 'David'), (2, 'Ali');
INSERT INTO c_accessories (computer_id, name) VALUES (1, 'mouse'), (1, 'keyboard'), (1, 'mouse'), (2, 'mouse'), (2, 'printer'), (2, 'monitor'), (3, 'speaker');

这是我的疑问:

SELECT 
    c.id
    ,c.name
    ,jsonb_agg(c_user.name)
    ,jsonb_agg(c_accessories.name)
FROM 
    computer c
JOIN 
    c_user ON c_user.computer_id = c.id
JOIN 
    c_accessories ON c_accessories.computer_id = c.id
GROUP BY c.id

我得到的结果是:

1   "HP"    ["John", "John", "John", "Elton", "Elton", "Elton", "David", "David", "David"]  ["mouse", "keyboard", "mouse", "mouse", "keyboard", "mouse", "mouse", "keyboard", "mouse"]
2   "Toshiba"   ["Ali", "Ali", ""Ali"]  ["monitor", "printer", "mouse"]

我想得到这个结果(如果数据库中存在重复项,则保留重复项)。还能够按用户和/或附件过滤计算机:

1 "HP" ["John", "Elton", "David"] ["keyboard", "mouse", "mouse"]
2 "Toshiba" ["Ali"] ["monitor", "printer", "mouse"]
3 "Dell" Null ["speaker"]

2 回复 | 直到 4 年前

Bergi 4 年前

使用子查询而不是联接:

SELECT 
    c.id,
    c.name,
    (SELECT
        jsonb_agg(c_user.name)
        FROM c_user
        WHERE c_user.computer_id = c.id
    ) AS user_names,
    (SELECT
        jsonb_agg(c_accessories.name)
        FROM c_accessories
        WHERE c_accessories.computer_id = c.id
    ) AS accessory_names
FROM 
    computer c

sticky bit 4 年前

将用户连接到一个派生表,该表执行计算机和附件的连接和聚合,然后再次进行聚合。

SELECT ca.id,
       jsonb_agg(u.name) AS users,
       ca.accessories
       FROM (SELECT c.id,
                    jsonb_agg(a.name) AS accessories
                    FROM computer AS c
                         LEFT JOIN c_accessories AS a
                                   ON a.computer_id = c.id
                    GROUP BY c.id) AS ca
            INNER JOIN c_user AS u
                       ON u.computer_id = ca.id
       GROUP BY ca.id,
                ca.accessories;

您还可以首先聚合包括用户和配件的ID,这样您就可以使用 DISTINCT 在聚合函数中,例如转换为记录数组。在子查询中重新聚合到JSON。

SELECT c.id,
       (SELECT jsonb_agg(x.name)
               FROM unnest(array_agg(DISTINCT row(u.id, u.name))) AS x
                                                                     (id integer,
                                                                      name text)) AS users,
       (SELECT jsonb_agg(x.name)
               FROM unnest(array_agg(DISTINCT row(a.id, a.name))) AS x
                                                                     (id integer,
                                                                      name text)) AS accessories
       FROM computer AS c
            LEFT JOIN c_accessories AS a
                      ON a.computer_id = c.id
            INNER JOIN c_user AS u
                       ON u.computer_id = c.id
       GROUP BY c.id;

db<>fiddle

a_horse_with_no_name 4 年前

首先进行聚合,然后联接到计算机表以获得聚合结果。

select c.id, c.name, 
       cu.users,
       ca.accessories
from computer c 
  left join (
    select computer_id, jsonb_agg(name) as users
    from c_user
    group by computer_id
  ) as cu on cu.computer_id = c.id
  left join (
    select computer_id, jsonb_agg(name) as accessories
    from c_accessories
    group by computer_id
  ) as ca on ca.computer_id = c.id