限制左连接返回一个结果? [英] Limiting a left join to returning one result?
问题描述
我目前将此左连接作为查询的一部分:
I currently have this left join as part of a query:
LEFT JOIN movies t3 ON t1.movie_id = t3.movie_id AND t3.popularity = 0
问题在于,如果有几部电影同名且受欢迎程度相同(别问,就是这样:-))然后返回重复的结果.
The trouble is that if there are several movies with the same name and same popularity (don't ask, it just is that way :-) ) then duplicate results are returned.
说了这么多,我想将左连接的结果限制为一个.
All that to say, I would like to limit the result of the left join to one.
我试过了:
LEFT JOIN
(SELECT t3.movie_name FROM movies t3 WHERE t3.popularity = 0 LIMIT 1)
ON t1.movie_id = t3.movie_id AND t3.popularity = 0
第二个查询因错误而终止:
The second query dies with the error:
Every derived table must have its own alias
我知道我问的有些含糊,因为我没有提供完整的查询,但我问的一般可能吗?
I know what I'm asking is slightly vague since I'm not providing the full query, but is what I'm asking generally possible?
推荐答案
错误很明显——你只需要在子查询结束后创建一个别名 )
并在你的 ) 中使用它code>ON
子句,因为每个表,无论是派生的还是真实的,都必须有自己的标识符.然后,您需要在子查询的选择列表中包含 movie_id
才能加入它.由于子查询已经包含 WHERE 流行度 = 0
,因此您无需将其包含在连接的 ON
子句中.
The error is clear -- you just need to create an alias for the subquery following its closing )
and use it in your ON
clause since every table, derived or real, must have its own identifier. Then, you'll need to include movie_id
in the subquery's select list to be able to join on it. Since the subquery already includes WHERE popularity = 0
, you don't need to include it in the join's ON
clause.
LEFT JOIN (
SELECT
movie_id,
movie_name
FROM movies
WHERE popularity = 0
ORDER BY movie_name
LIMIT 1
) the_alias ON t1.movie_id = the_alias.movie_id
如果您在外部 SELECT
中使用这些列之一,例如通过 the_alias.movie_name
引用它.
If you are using one of these columns in the outer SELECT
, reference it via the_alias.movie_name
for example.
要让每个组加入一个,您可以在 movie_id
和组上使用聚合 MAX()
或 MIN()
它在子查询中.不需要子查询 LIMIT
- 您将收到每个名称的第一个 movie_id
和 MIN()
或最后一个 MAX()
.
To get one per group to join against, you can use an aggregate MAX()
or MIN()
on the movie_id
and group it in the subquery. No subquery LIMIT
is then necessary -- you'll receive the first movie_id
per name withMIN()
or the last with MAX()
.
LEFT JOIN (
SELECT
movie_name,
MIN(movie_id) AS movie_id
FROM movies
WHERE popularity = 0
GROUP BY movie_name
) the_alias ON t1.movie_id = the_alias.movie_id
这篇关于限制左连接返回一个结果?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!