将左联接限制为返回一个结果? [英] 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.

所有这些，我想将左联接的结果限制为1.

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?

推荐答案

错误很明显-您只需要在子查询关闭)之后为其创建别名，并在您的ON子句中使用它，因为每次派生表或实表必须具有自己的标识符.然后，您需要在子查询的选择列表中包括movie_id才能加入它.由于子查询已经包含WHERE popularity = 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了-您将通过MIN()接收每个名称的第一个movie_id或通过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屋！