php 限制左连接返回一个结果？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/11388443/
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
Limiting a left join to returning one result?
提问by Nate
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.
尽管如此，我想将左连接的结果限制为一个。
I tried this:
我试过这个：
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?
我知道我问的有点含糊，因为我没有提供完整的查询，但我问的一般可能吗？
回答by Michael Berkowski
The error is clear -- you just need to create an alias for the subquery following its closing )and use it in your ONclause since every table, derived or real, must have its own identifier. Then, you'll need to include movie_idin 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 ONclause.
错误很明显——您只需要在关闭后为子查询创建一个别名)并在您的ON子句中使用它，因为每个表，派生的或真实的，都必须有自己的标识符。然后，您需要包含movie_id在子查询的选择列表中才能加入它。由于子查询已经包含WHERE popularity = 0，您不需要将其包含在连接的ON子句中。
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
If you are using one of these columns in the outer SELECT, reference it via the_alias.movie_namefor example.
如果您在外部使用这些列之一SELECT，请通过the_alias.movie_name例如引用它。
Update after understanding the requirement better:
更好地理解需求后更新：
To get one per group to join against, you can use an aggregate MAX()or MIN()on the movie_idand group it in the subquery. No subquery LIMITis then necessary -- you'll receive the first movie_idper name withMIN()or the last with MAX().
要让每个组加入一个，您可以使用聚合MAX()或MIN()在movie_id子查询中将其分组。不需要子查询LIMIT——您将收到movie_id每个名称的第一个MIN()或最后一个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
回答by Erik Olson
LEFT JOIN movies as m ON m.id = (
SELECT id FROM movies mm WHERE mm.movie_id = t1.movie_id
ORDER BY mm.id DESC
LIMIT 1
)
回答by i--
you could try to add GROUP BY t3.movie_idto the first query
您可以尝试添加GROUP BY t3.movie_id到第一个查询
回答by Preet Sangha
Try this:
尝试这个：
LEFT JOIN
(
SELECT t3.movie_name, t3.popularity
FROM movies t3 WHERE t3.popularity = 0 LIMIT 1
) XX
ON t1.movie_id = XX.movie_id AND XX.popularity = 0
