從 N:M 關係中獲取給定組的成員

我有這種 N:M 關係：

CREATE TABLE auth_user (
   id integer NOT NULL PRIMARY KEY,
   username character varying(150) NOT NULL UNIQUE
);

CREATE TABLE auth_group (
   id integer NOT NULL PRIMARY KEY,
   name character varying(80) NOT NULL UNIQUE
);

CREATE TABLE auth_user_groups (
   id integer NOT NULL PRIMARY KEY,
   user_id integer REFERENCES auth_user(id) NOT NULL,
   group_id integer REFERENCES auth_group(id) NOT NULL,
   CONSTRAINT user_groups UNIQUE(user_id, group_id)
);

INSERT INTO auth_user VALUES (1, 'user1');
INSERT INTO auth_user VALUES (2, 'user2');
INSERT INTO auth_group VALUES (1, 'group1');
INSERT INTO auth_group VALUES (2, 'group2');
INSERT INTO auth_user_groups VALUES (1, 1, 1);
INSERT INTO auth_user_groups VALUES (2, 2, 1);
INSERT INTO auth_user_groups VALUES (3, 2, 2);

如何選擇組“group1”中的所有使用者名？

我使用 PostgreSQL，但首選適用於任何地方的 SQL。

此EXISTS查詢返回唯一使用者，無論 中是否存在重複條目auth_user_groups。

SELECT *
FROM   auth_user u
WHERE  EXISTS (
  SELECT 1
  FROM   auth_user_groups
  WHERE  user_id = u.id
  AND    group_id = (SELECT id FROM auth_group WHERE name = 'group1')
  );

通常也很快。

筆記

對於 Postgres - 您似乎正在使用它。

索引列的順序很重要。您定義UNIQUE(user_id, group_id)了 ，它使用相應的唯一索引實現。對於您的特定查詢，(group_id, user_id)最好使用索引。

您可以切換UNIQUE約束的列，或者創建一個額外的（可選唯一的）索引，如果您需要兩者（這是常見情況），則列顛倒。有關的：

• 複合索引是否也適用於第一個欄位的查詢？

另請注意，UNIQUE約束中包含的列仍然可以是**NULL**. （與自動生成PRIMARY KEY所有成員列的約束不同NOT NULL！）您通常也希望user_id這樣group_id做。有關的：auth_user_groups``NOT NULL

• 如何在 PostgreSQL 中實現多對多關係？

引用自：https://dba.stackexchange.com/questions/202254