如何有效地M:N连接3个表

Question

我有几张表：

应用程序：

id | name | url_key
===================
1  | Hello World | hello-world
2  | Snake 2 | snake-2

开发人员：

id | name | url_key
===================
1  | Mr. Robinson | mr-robinson
2  | Richard | richard
3  | Nokia | nokia

应用程序开发人员分配

app_id | developer_id
=====================
1      | 1
1      | 2
2      | 3

因此，正如您所见，一个应用程序可能有多个开发人员。

我真正需要的是编写一个 PostgreSQL 查询来选择所有具有相关开发人员有用信息的应用程序。 例如：

Hello World | 1-hello-world | Mr.Robinson<1-mr-robinson> /and 1 more/
Snake 2     | 2-snake-2     | Nokia<3-nokia>

如果应用程序有一个开发人员，只需将开发人员的名称、url key 和 id 附加到应用程序中，如果有更多开发人员，则附加第一个并附加“有多少开发人员”的信息。

最重要的是 - 我需要最终过滤结果（例如，不返回某些指定开发人员的应用程序）。

我知道可以通过为应用程序本身编写第一个查询然后为每行发送下一个查询来解决这个问题，但我认为这不是一个好的方法...

如果我只是简单地加入表：

SELECT * FROM apps a
LEFT JOIN apps_developers_assignment ada ON a.id = ada.app_id
LEFT JOIN developers d ON ada.developer_id = d.id

Hello World | 1-hello-world | Mr.Robinson<1-mr-robinson>
Hello World | 1-hello-world | Richard<2-richard>
Snake 2     | 2-snake-2     | Nokia<3-nokia>

它会返回重复的应用程序...并且我不知道开发人员如何过滤这些结果（正如我上面写的）。

Answer 1

假设您只想过滤掉一名开发人员，则需要传递一个参数。为此，我将此参数/变量称为 @excludeDevId。

我对 T-SQL 更熟悉，因此这可能/也需要 Postgres 中的等效语法。另外，由于左连接为空，我将保留空值 - 从您的输出中，您可能想要合并（可能还有其他一些输出调整）。这是即兴的且未经测试的，但至少应该足以让您朝着正确的方向前进：

SELECT 
    a.name AppName, 
    a.id AppId,
    d.id DevId,
    d.url_key Url,
    d.name DevName,
    CAST(agg.dev_count - 1 as VARCHAR) + ' more'
FROM 
    (
    SELECT 
        MAX(ada.developer_id) first_dev_id, 
        COUNT(ada.developer_id) dev_count, 
        app_id
    FROM
        apps_developers_assignment ada 
    WHERE 
        d.id != @excludeDevId
    GROUP BY
        app_id
    ) agg
    INNER JOIN apps a ON a.id = agg.app_id
    LEFT JOIN developers d ON agg.developer_id = d.id

Answer 2

如果您想使用左连接，则需要将条件放在连接上，否则它将创建一个内部连接，并且不会返回包含 null 的行...尝试如下操作：

SELECT * FROM apps a
LEFT JOIN apps_developers_assignment ada ON a.id = ada.app_id
                                            AND ada.developerid = :devid

LEFT JOIN developers d ON ada.developer_id = d.id

WHERE a.id = :appid