SQL查询至少一个东西 [英] SQL query at least one of something

问题描述

我有一堆用户，每个人都有很多帖子。
模式：

I have a bunch of Users, each of whom has many Posts. Schema:

Users: id
Posts: user_id, rating

如何找到至少有一条评分高于10的帖子的用户？

How do I find all Users who have at least one post with a rating above, say, 10?

我不知道我是否应该使用subQuery，或者有更简单的方法。

I'm not sure if I should use a subQuery for this, or if there's an easier way.

谢谢！

推荐答案

要查找至少有一条评分大于10的帖子的所有用户，请使用：

To find all users with at least one post with a rating above 10, use:

SELECT u.*
  FROM USERS u
 WHERE EXISTS(SELECT NULL
                FROM POSTS p
               WHERE p.user_id = u.id
                 AND p.rating > 10)

EXISTS不关心SELECT语句在其中 - 你可以用1/0替换NULL，这应该导致一个数学错误除以零...但它不会，因为EXISTS只关心WHERE子句中的过滤。

EXISTS doesn't care about the SELECT statement within it - you could replace NULL with 1/0, which should result in a math error for dividing by zero... But it won't, because EXISTS is only concerned with the filteration in the WHERE clause.

相关性（WHERE p.user_id = u.id）是为什么被称为关联子查询的原因，并且将仅返回USERS表中的id值匹配，除了评分比较。

The correlation (the WHERE p.user_id = u.id) is why this is called a correlated subquery, and will only return rows from the USERS table where the id values match, in addition to the rating comparison.

EXISTS也更快，根据情况，因为它返回true，只要符合条件 - 重复不

EXISTS is also faster, depending on the situation, because it returns true as soon as the criteria is met - duplicates don't matter.

这篇关于SQL查询至少一个东西的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！