sql - 从参考/连接表中获取所有许多：许多关系

Question

我很难在多：多场景中查询可能的关系。

我提出我的架构：

我所知道的如何使用此模式进行查询是：

• 给定用户所属的所有频段。
• 属于给定频段的所有用户。

我想做的是：

• 获取给定用户所属的所有乐队中的所有乐队成员。
• 即，假设我在 5 个乐队中，我想知道我所有的乐队成员是谁。

我的第一个问题是：

• 这种类型的查询有名称吗？我对加入的关系比我加入的更感兴趣（只是说这让我想把整个系统放入一个图形数据库：/）？我想学习正确的术语来帮助我在谷歌上查找问题。
• 一般来说，这在 RDBMS 领域是一个糟糕的想法吗？我觉得这应该是一个常见的用例，但我想知道我是否完全接近这个错误。

回顾一下：

• 我希望查询上述模式，预期输出是每个用户一行作为给定用户共享一个乐队的乐队成员。

标签： sqlpostgresqlmany-to-manypostgresql-9.6

Answer 1

术语

您的术语似乎是正确的-“多对多”，通常写为带有冒号的“多：多”。有时中间表（band_members）被称为“桥表”。

您可能可以 drop band_members.id，因为这两个外键也构成了一个复合主键（并且主键实际上可以这样定义，因为通常一个 User 不能成为同一个 Band 的成员两次。唯一的例外是如果一个用户可以在同一个乐队中拥有多个角色）。

解决方案

从表面上看，这听起来很简单——我们可以看到表之间的关系，通常只需INNER JOIN在它们之间使用 an。有三个表，所以这将是两个连接。

但是，我们必须首先正确地概念化问题。我们遇到的问题是用户和乐队成员（用户 ID）之间的连接实际上用于两件事：

• 哪个用户在哪个频段
• 按用户过滤

所以要做到这一点，我们需要引入一个具有多种用途的表：

SELECT
    Users.first_name, Users.last_name
FROM Users
INNER JOIN Band_Members Band_Members1 ON (Band_Members1.user_id = Users.Id)
INNER JOIN Band_Members Band_Members2 ON (Band_Members1.band_id = Band_Members2.band_id)
WHERE
    Band_Members2.user_id = 1

你可以在这里看到我已经加入了Band_Members两次，当一个人这样做时，一个人必须给它们起不同的别名，这样它们就可以被单独引用。第一个实例在用户表和桥表之间进行了明显的连接，第二个实例在“用户所在的乐队”和“我所在的乐队”之间建立了链接。

当然，此解决方案要求您知道您的用户 ID。如果您想要执行类似的查询但基于您的姓名进行过滤，那么您将不得不加入另一个（重新别名）用户表副本，以便您可以区分两个不同的目的：“用户在乐队中”和“您的用户”。