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屋!