MySQL复杂Where语句

Question

我正在尝试从用户不在的组中获取组名和组类型。在我添加"AND NOT EXISTS“语句之前，一切都正常，但我不想要每个组，只想要用户不在其中的组。

Select g.groupName AS groupName, g.groupType AS groupType, COUNT(*) AS total from Groups AS g
JOIN Group_Interest AS gi ON gi.groupId=g.groupId WHERE gi.interestId IN (
     Select interestId FROM User_Interest WHERE userId=214
) AND NOT EXISTS (
     SELECT groupName FROM Groups AS g JOIN User_Joined_Group AS ujg ON ujg.groupId=g.groupId WHERE userId=214
)
GROUP BY groupName
ORDER BY COUNT(*) DESC
LIMIT 10;

Answer 1

这个查询应该是有效的：

SELECT g.groupName AS groupName, g.groupType AS groupType, COUNT(*) AS total 
FROM Groups AS g
JOIN Group_Interest AS gi ON gi.groupId = g.groupId 
JOIN User_Interest AS ui ON gi.interestId = ui.interestId
JOIN User_Joined_Group AS ujg ON ujg.groupId = g.groupId
WHERE ui.userId = 214 AND ujg.userId != 214
GROUP BY groupName, groupType
ORDER BY COUNT(*) DESC
LIMIT 10;

这样的查询也应该有效：

SELECT g.groupName AS groupName, g.groupType AS groupType, COUNT(*) AS total 
FROM Groups AS g
JOIN Group_Interest AS gi ON gi.groupId = g.groupId 
WHERE gi.interestId IN (
     SELECT interestId FROM User_Interest WHERE userId = 214
) AND NOT EXISTS (
     SELECT 1 FROM User_Joined_Group AS ujg ON ujg.groupId = g.groupId WHERE ujg.userId = 214)
GROUP BY groupName, groupType
ORDER BY COUNT(*) DESC
LIMIT 10;