查询不在特定组中的用户? (想使用EXCEPT，但似乎MySQL不支持它)

问题描述

我与用户和组之间有很多关系:

I have a many to many relationship between users and groups:

CREATE TABLE IF NOT EXISTS `SecurityIdentifiers` (
  `Guid` char(36) NOT NULL,
  PRIMARY KEY  (`Guid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS `CaseIdUsers` (
  `Sid` char(36) NOT NULL,
  `Acl` int(11) NOT NULL,
  `FirstName` varchar(45) NOT NULL,
  `LastName` varchar(45) NOT NULL,
  `CaseID` varchar(8) NOT NULL,
  PRIMARY KEY  (`Sid`),
  UNIQUE KEY `CaseID_UNIQUE` (`CaseID`),
  KEY `fk_CaseIDUsers_AccessControlLists1` (`Acl`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS `GroupMembers` (
  `User` char(36) NOT NULL,
  `Group` char(36) NOT NULL,
  PRIMARY KEY  (`User`,`Group`),
  KEY `fk_Groups_has_SecurityIdentifiers_SecurityIdentifiers1` (`User`),
  KEY `fk_Groups_has_SecurityIdentifiers_Groups1` (`Group`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS `Groups` (
  `Sid` char(36) NOT NULL,
  `Acl` int(11) NOT NULL,
  `Name` varchar(45) NOT NULL,
  `Description` varchar(255) NOT NULL,
  PRIMARY KEY  (`Sid`),
  UNIQUE KEY `Name_UNIQUE` (`Name`),
  KEY `fk_Groups_Access Control Lists1` (`Acl`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

我需要找到不在特定组中的所有用户.我想做类似的事情:

I need to find all the users who aren't in a specific group. I wanted to do something like:

SELECT CaseId FROM CaseIdUsers
EXCEPT
SELECT CaseId FROM CaseIdUsers
JOIN GroupMembers ON GroupMembers.User = CaseIdUsers.Sid
JOIN Groups ON GroupMembers.Group = Groups.Sid
WHERE Groups.Name = 'MyGroupName'

但是MySQL不支持EXCEPT.我现在该怎么办?

But MySQL doesn't support EXCEPT. What do I do now?

推荐答案

您可以使用 NOT IN() 或 <> ANY()

You can use a NOT IN(), or a <> ANY()

SELECT CaseId
FROM CaseIdUsers
WHERE CaseID NOT IN (
  SELECT CaseId 
  FROM CaseIdUsers
    JOIN GroupMembers ON GroupMembers.User = CaseIdUsers.Sid
    JOIN Groups ON GroupMembers.Group = Groups.Sid
  WHERE Groups.Name = 'MyGroupName'
)

或者，您可以将LEFT JOIN与GROUP BY

SELECT CaseId 
FROM CaseIdUsers
  LEFT JOIN GroupMembers ON GroupMembers.User = CaseIdUsers.Sid
  LEFT JOIN Groups ON GroupMembers.Group = Groups.Sid
    AND Groups.Name = 'MyGroupName'
HAVING COUNT(Groups.Sid) = 0

这篇关于查询不在特定组中的用户? (想使用EXCEPT，但似乎MySQL不支持它)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！