如何只显示我的结果表的多个最大值？

Question

在我的数据库上使用以下MySQL查询：

SELECT movie.name, SUM(heroes.likes) AS 'success'
FROM heroebymovie JOIN 
     heroes
     ON heroes.ID = heroebymovie.heroID JOIN
     movie
     ON movie.ID = heroebymovie.movieID
GROUP BY movie.ID
ORDER BY SUM(heroes.likes) DESC

我得到了这样的结果：

|name                       |success  |
|Avengers 2                 |72317559 |
|Avengers                   |72317559 |
|Captain America : Civil War|67066832 |

我只想显示“成功”次数最多的电影(在本例中是“复联2”和“复联”)。有人能解释一下做这件事的方法吗？

Answer 1

一种简单的方法是使用having子句来过滤最大值(在本例中是sum desc limit 1的有序列表)

SELECT movie.name, SUM(heroes.likes) AS success
FROM heroebymovie JOIN heroes ON heroes.ID = heroebymovie.heroID 
JOIN movie ON movie.ID = heroebymovie.movieID
GROUP BY movie.ID 
HAVING success = (
    SELECT SUM(heroes.likes) 
    FROM heroebymovie JOIN heroes ON heroes.ID = heroebymovie.heroID 
    JOIN movie ON movie.ID = heroebymovie.movieID
    GROUP BY movie.ID
    ORDER BY SUM(heroes.likes) DESC
    LIMIT 1
)
ORDER BY SUM(heroes.likes) DESC

Answer 2

您正在寻找限制，但希望考虑平局。MySQL支持LIMIT子句，但不幸的是没有附带的ties表达式。

在标准SQL中，您只需添加

FETCH 1 ROW WITH TIES;

然后就完事了。(SQL Server对TOP(1) WITH TIES也是这样做的。)

另一种方法是使用标准SQL的MAX OVER：MAX(SUM(heroes.likes)) OVER()，并且只保留总和与最大值匹配的行。或者使用RANK OVER。但是，MySQL同样不支持这两种方法。

因此，您的主要选择是执行查询两次，如下面的伪代码所示：

select sum ... having sum = (select max(sum) ...)

在MySQL中获得最大和的一种简单方法是按和降序排序，并将结果限制在一行中。

SELECT m.name, SUM(h.likes) AS "success"
FROM heroebymovie hm
JOIN heroes h ON h.ID = hm.heroID
JOIN movie m ON m.ID = hm.movieID
GROUP BY m.ID
HAVING SUM(h.likes) =
(
  SELECT SUM(h2.likes)
  FROM heroebymovie hm2
  JOIN heroes h2 ON h2.ID = hm2.heroID
  GROUP BY hm2.movieID
  ORDER BY SUM(h2.likes) DESC
  LIMIT 1
);