MySQL SQL 查询至少其中一项
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3197322/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQL query at least one of something
提问by atp
I have a bunch of Users, each of whom has many Posts. Schema:
我有一堆用户，每个人都有很多帖子。架构：
Users: id
Posts: user_id, rating
How do I find all Users who have at least one post with a rating above, say, 10?
我如何找到至少有一篇文章评分高于 10 的所有用户？
I'm not sure if I should use a subQuery for this, or if there's an easier way.
我不确定是否应该为此使用子查询，或者是否有更简单的方法。
Thanks!
谢谢！
回答by OMG Ponies
To find all users with at least one post with a rating above 10, use:
要查找至少一篇评分高于 10 的帖子的所有用户，请使用：
SELECT u.*
FROM USERS u
WHERE EXISTS(SELECT NULL
FROM POSTS p
WHERE p.user_id = u.id
AND p.rating > 10)
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.
EXISTS 不关心其中的 SELECT 语句 - 您可以用 1/0 替换 NULL，这应该会导致除以零的数学错误......但它不会，因为 EXISTS 只关心过滤WHERE 子句。
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.
相关性（WHERE p.user_id = u.id）就是为什么这被称为相关子查询，除了评分比较之外，它只会返回来自 USERS 表中 id 值匹配的行。
EXISTS is also faster, depending on the situation, because it returns true as soon as the criteria is met - duplicates don't matter.
EXISTS 也更快，具体取决于情况，因为只要满足条件，它就会返回 true - 重复无关紧要。
回答by Ike Walker
You can join the tables to find the relevant users, and use DISTINCT so each user is in the result set at most once even if they have multiple posts with rating > 10:
您可以加入表格以查找相关用户，并使用 DISTINCT 使每个用户最多出现一次，即使他们有多个评分 > 10 的帖子：
select distinct u.id,u.username
from users u inner join posts p on u.id = p.user_id
where p.rating > 10
回答by Jon Weers
Use an inner join:
使用内部联接：
SELECT * from users INNER JOIN posts p on users.id = p.user_id where p.rating > 10;
回答by xagyg
select distinct id
from users, posts
where id = user_id and rating > 10
回答by Zak
SELECT max(p.rating), u.id
from users u
INNER JOIN posts p on users.id = p.user_id
where p.rating > 10
group by u.id;
Additionally, this will tell you what their highest rating is.
此外，这会告诉您他们的最高评分是多少。
回答by orbfish
The correct answer for your question as stated is OMG Ponies's answer, WHERE EXISTS is more descriptive and almost always faster. But "SELECT NULL" looks really ugly and counterintuitive to me. I've seen SELECT * or SELECT 1 as a best practice for this.
如上所述，您的问题的正确答案是 OMG Ponies 的答案，WHERE EXISTS 更具描述性并且几乎总是更快。但是“SELECT NULL”对我来说看起来非常丑陋且违反直觉。我已经看到 SELECT * 或 SELECT 1 作为最佳实践。
Another way, in case we're collecting answers:
另一种方式，以防我们收集答案：
SELECT u.id
FROM users u
JOIN posts p on u.id = p.user_id
WHERE p.rating > 10
GROUP BY u.id
HAVING COUNT(*) > 1
This could be useful if it's not always 1 you're testing on.
如果它不总是 1 您正在测试，这可能很有用。
