主页 / dba / 问题 / 202254
Accepted
guettli
Asked: 2018-03-26 23:57:55 +0800 CST

从 N:M 关系中获取给定组的成员

  • 772

我有这个 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。

postgresql join

5 个回答

  1. Best Answer

    此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_idinauth_user_groups也是NOT NULL如此。有关的：

    • 8

  2. 是这样的：

    select *
from auth_user
where id in (select aug.user_id
             from auth_group ag
               join auth_user_groups aug on aug.group_id = ag.id
             where ag.name = 'group1');
    • 6

  3. A_horse_with_no_name 发布了一个很好的答案，事实上这是我的主要选择（值得一票支持=））

    您也可以按照通常的方式进行操作：

    select au.*
from dbo.auth_user au
join dbo.auth_user_groups aug on aug.[user_id] = au.id
join dbo.auth_group ag on ag.id = aug.group_id
where ag.[name] = 'group1'

    但请注意，如果您有多个同名组和同名组中的用户，您将得到重复的行：

    INSERT INTO auth_group VALUES (3, 'group1');
INSERT INTO auth_user_groups VALUES (1, 1, 3);

    在这种情况下，您应该使用不同的：

    select distinct au.*
from dbo.auth_user au
join dbo.auth_user_groups aug on aug.[user_id] = au.id
join dbo.auth_group ag on ag.id = aug.group_id
where ag.[name] = 'group1'

    注意 A_Horse 答案在这种情况下仍然有效。

    • 2

  4. 从 SQL Server 2017 和 Azure SQL DB，您可以使用新的图形数据库功能和新的 MATCH 子句来回答这样的查询，例如

    SELECT FORMATMESSAGE ( 'User %s (%i) is in group %s.', [user].userName, [user].userId, [group].groupName ) msg
FROM dbo.users [user], dbo.hasGroup hasGroup, dbo.groups [group]
WHERE [group].groupName = 'group1'
  AND MATCH ( [user]-(hasGroup)->[group] );

    我的结果：

    我的结果

    此处提供完整脚本。

    • 2 
  5. select username from auth_user, auth_user_groups, auth_group where 
  auth_group.name='group1' and 
  auth_user_groups.group_id=auth_group.id and
  auth_user.id=auth_user_groups.user_id;
    • 0

相关问题