MySQL Complex Where Statement

Question

我试图从用户不在的组中获取组名和组类型；并且不存在"；声明，但我不想要每个组，只想要用户不在的组。

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;