mysql - 如果用户子集中的任何一个给某个项目特定的评级，如何从 mySQL 选择中排除项目？

Question

我正在开发一个小型网络应用程序，您可以在其中对餐厅进行评分或对您想要尝试它们的程度进行评分。我的数据库设置如下:

users (name, userid)


ratings(userid,restaurantid,rating(#0-5),visited(boolean))

(如果您以前没有去过一家餐厅，但给它打了 0-5 分，系统会根据您对它的喜爱程度来对待它)

我希望能够将您的评分/愿望 list 与其他用户进行比较，但我遇到了一些问题。我知道如何加入表格并获得比较中每个用户的评分总和，以便使用此评分最高的餐厅浮到顶部:

SELECT ratings.rest_id, SUM( ratings.rating ) AS Sum
FROM names
LEFT JOIN ratings ON ratings.rest_id = names.id
WHERE ratings.user_id IN ( userid1, userid2, userid3 ) 
GROUP BY ratings.rest_id
ORDER BY  `Sum` DESC , names.name ASC  

但我希望用户能够修改搜索，以便它只包括所有包含的用户都设置为“访问过”的餐厅(这样你就可以看到所有包含的用户最喜欢的餐厅)实际上去过)或“未去过”(这样您就可以列出尚未有人去过但每个人都想尝试最多的地方)。

如果我只是在 WHERE 的末尾添加一个“AND ratings.visited != 1”子句，它将排除将已访问列设置为 1(已访问)的特定用户的评级，但它仍将包括其他用户对该餐厅的评分(我不想要)。例如，如果您有以下设置:

User | Restaurant | rating | visited
------------------------------------
pam  |   subway   |   5    |    1
joe  |   subway   |   3    |    0
bob  |   subway   |   1    |    0

pam  |   bigboy   |   4    |    0
joe  |   bigboy   |   3    |    0
bob  |   bigboy   |   4    |    0

pam  |  tacobell  |   2    |    1
joe  |  tacobell  |   2    |    1
bob  |  tacobell  |   5    |    1

目前通过添加“AND ratings.visited != 1”子句并运行上述搜索，结果将是:

rest_id | Sum
-------------
subway  |  4
bigboy  | 11

尽管 Pam 去过地铁，但仍包括地铁(仅排除了她的评分)。当我想要的是搜索只返回 bigboy 的总和时，因为它是所有包含的访客从未去过的唯一一家餐厅。

谁能给我指出正确的方向？

谢谢!杰里米

Answer 1

您可以使用 NOT EXISTS() 解决此问题:

SELECT ratings.rest_id, SUM( ratings.rating ) AS Sum
FROM names
LEFT JOIN ratings ON ratings.rest_id = names.id
 AND ratings.user_id IN ( userid1, userid2, userid3 ) 
WHERE NOT EXISTS(SELECT 1 FROM ratings r 
                 WHERE r.visited = 1 AND r.rest_id = ratings.rest_id)
GROUP BY ratings.rest_id
ORDER BY  `Sum` DESC , names.name ASC

此外，如您所见，我已将 ratings.user_id IN ( userid1, userid2, userid3 ) 条件移至 ON 子句。左连接时，右表的条件应该在ON中，当在WHERE子句中指定时，左连接自动转为内连接。