如何不使用子查询，通过LEFT OUTER JOIN获取各国最长时长影片

我原本用下面的SQL语句获取每个国家时长最长的电影信息（包含电影名、国家名、时长）：

SELECT t1.FilmName, t2.CountryName, t1.FilmRunTimeMinutes 
FROM Film as t1 
INNER JOIN country as t2 on t1.FilmCountryId = t2.CountryID 
WHERE t1.FilmRunTimeMinutes = ( 
    SELECT max(t2.FilmRunTimeMinutes) 
    FROM film as t2 
    WHERE t2.FilmCountryId = t1.FilmCountryId 
) 
ORDER BY FilmRunTimeMinutes DESC

现在我想改用LEFT OUTER JOIN实现完全相同的结果集，并且不使用子查询，请问该怎么写？

表结构信息： #

• Film表：
  • FilmId -- 主键(PK)
  • FilmName
  • FilmCountryId -- 外键(FK)，关联Country表的CountryId
  • FilmRunTimeMinutes
• Country表：
  • CountryId -- 主键(PK)
  • CountryName

没问题，我们可以通过自连接Film表+LEFT JOIN的方式来实现，核心思路是：找到每个国家中，不存在其他同国家电影时长比它更长的记录——这样的记录就是该国家时长最长的电影。

具体SQL语句如下：

SELECT t1.FilmName, c.CountryName, t1.FilmRunTimeMinutes
FROM Film t1
INNER JOIN Country c ON t1.FilmCountryId = c.CountryId
LEFT OUTER JOIN Film t2 
  ON t1.FilmCountryId = t2.FilmCountryId 
  AND t1.FilmRunTimeMinutes < t2.FilmRunTimeMinutes
WHERE t2.FilmId IS NULL
ORDER BY t1.FilmRunTimeMinutes DESC;

逻辑解释： #

1. 首先用INNER JOIN关联Film和Country表，获取基础的电影-国家对应信息；
2. 然后用LEFT OUTER JOIN把Film表自连接起来，关联条件是同一个国家，并且当前电影的时长小于另一部同国电影的时长；
3. 最后通过WHERE t2.FilmId IS NULL筛选出那些没有找到更长时长同国电影的记录——这些就是每个国家里时长最长的电影；
4. 最后按时长降序排序，和你原来的SQL结果完全一致。

这个写法既没有用子查询，又通过LEFT JOIN实现了需求，而且如果同一个国家有多个电影时长并列最长的情况，也会全部返回，和你原来的子查询逻辑完全匹配~

内容的提问来源于stack exchange，提问作者aprkturk