我试图了解这个查询,但其中一条记录仍然不断弹出。总之,我的意图是在左边显示组成员,并在右边显示电话簿中而非的姓名。这样,用户就可以选择它们并将它们添加到组中。
user_id firstname group_id grpname
------- --------- -------- ----------
1 Luker 3 Abc
2 John 1 Some Group
3 Sam 2 Awesome Group
4 Mitch 1 Some Group
4 Mitch 2 Awesome Group
5 Rocky (NULL) (NULL)
6 Pops (NULL) (NULL)
唯一的是,如果其中一个用户是多个组(user_id 4)的一部分,那么他们的名字根本不应该显示在电话簿中,因为它已经被放在现有成员的列表中了。
-- Query for group_id 2
SELECT user.id user_id, user.firstname, grp.id group_id, grp.grpname FROM agi_user user
LEFT JOIN agi_group_user gu ON user.id = gu.user_id
LEFT JOIN agi_groups grp ON gu.group_id = grp.id
WHERE grp.id IS NULL OR grp.id != 2
GROUP BY user.id
但由于某种原因,用户Mitch仍然不断弹出。
user_id firstname group_id grpname
------- --------- -------- ----------
1 Luker 3 Abc
2 John 1 Some Group
4 Mitch 1 Some Group
5 Rocky (NULL) (NULL)
6 Paps (NULL) (NULL)
编辑::我需要的输出是
user_id firstname group_id grpname
------- --------- -------- ----------
1 Luker 3 Abc
2 John 1 Some Group
5 Rocky (NULL) (NULL)
6 Paps (NULL) (NULL)
基本上,我想让所有不属于该组的用户包括NULL。但是,由于一个用户是多个组的一部分,她的记录在不应该出现的时候仍然会出现,因为另一个组的group_id可能为3或4(或除2之外的任何其他组)。
您可以使用NOT EXIST
检查组2和给定用户是否根本没有组/用户行
SELECT
user.id user_id,
user.firstname,
grp.id group_id,
grp.grpname
FROM
agi_user user
LEFT JOIN agi_group_user gu ON user.id = gu.user_id
LEFT JOIN agi_groups grp ON gu.group_id = grp.id
WHERE
NOT EXISTS (
SELECT 'x' FROM agi_group_user x
WHERE
x.user_id = user.user_id and
x.group_id = 2)
GROUP BY
user.id
请注意,如果用户不在第2组中,而是在另外两个组中,则他将在该结果中出现1次,并且只返回他所在的组中的一个。要解决此问题,可以将grp.id添加到GROUP BY
子句中,也可以使用GROUP_CONCAT
在单个字段中返回组名列表。
或者,这也应该起作用,在MySQL中,它甚至可能表现得更好,因为它在子查询方面很糟糕。不过,就我个人而言,我认为这在语义上还不太清楚。它第二次加入用户/组表,但将group_id(2)添加到加入中。如果没有为此表返回任何行,则用户不在组2中。关于GROUP_CONCAT
的注释也适用于此查询。
SELECT
user.id user_id,
user.firstname,
grp.id group_id,
grp.grpname
FROM
agi_user user
LEFT JOIN agi_group_user gu ON user.id = gu.user_id
LEFT JOIN agi_groups grp ON gu.group_id = grp.id
LEFT JOIN agi_group_user x ON user.id = x.user_id and x.group_id = 2
WHERE
x.group_id IS NULL
GROUP BY
user.id
如果我理解正确,您希望这样:"不在组2中的所有用户"
这被称为反半联接(或只是反联接),可以通过LEFT JOIN /IS NULL
查询来完成:
SELECT u.id user_id, u.firstname
FROM agi_user AS u
LEFT JOIN agi_group_user AS gu
ON u.id = gu.user_id
AND gu.group_id = 2
WHERE gu.group_id IS NULL ;
或者具有NOT EXISTS
子查询:
SELECT u.id user_id, u.firstname
FROM agi_user AS u
WHERE NOT EXISTS
( SELECT 1
FROM JOIN agi_group_user AS gu
WHERE u.id = gu.user_id
AND gu.group_id = 2
) ;