MySQL SUM 和 GROUP BY 生成的列上的 MAX
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1766309/
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
MAX on columns generated by SUM and GROUP BY
提问by BeaversAreDamned
I'm trying to get the MAX on a column which is generated dynamically using the SUM statement. The SUM statement is used together with the 'GROUP by' syntax.
我试图在使用 SUM 语句动态生成的列上获取 MAX。SUM 语句与“GROUP by”语法一起使用。
This is the original query, however it needs to be modified to work with grouping, sums and of course MAX.
这是原始查询，但需要对其进行修改才能使用分组、总和，当然还有 MAX。
SELECT SUM(video_plays) AS total_video_plays
FROM `video_statistics` v_stat
GROUP BY v_stat.`video_id` ASC
As you can see SUM is adding all the values inside video_playsas total_video_plays..
如您所见，SUM 将video_plays 中的所有值添加为total_video_plays..
But I SIMPLY want to get the MAX of total_video_plays
但我只是想获得total_video_plays的最大值
My attempts are below, however they do not work..
我的尝试如下，但是它们不起作用..
SELECT SUM(video_plays) AS MAX(total_video_plays)
FROM `video_statistics` v_stat
GROUP BY v_stat.`video_id` ASC
How would you get the MAX on a column made dynamically without using subqueries- Because the above is already placed within one.
如何在不使用子查询的情况下动态生成列上的 MAX - 因为上面已经放置在一个列中。
回答by Larry Lustig
Something like
就像是
SELECT SUM(video_plays) AS total_video_plays
FROM `video_statistics` v_stat
GROUP BY v_stat.`video_id`
ORDER BY total_video_plays DESC
LIMIT 1
Hat Tip OMG Ponies for proper MySQL dialect.
帽子提示 OMG 小马用于正确的 MySQL 方言。
回答by OMG Ponies
You can not do what you're asking without a subquery, because you can't run two aggregate functions, one on top of the other.
如果没有子查询，您将无法执行您所要求的操作，因为您无法运行两个聚合函数，一个在另一个之上。
回答by Wes
Will this work for you?
这对你有用吗？
SELECT MAX(total_video_plays) from table (
SELECT SUM(video_plays) AS total_video_plays
FROM `video_statistics` v_stat
GROUP BY v_stat.`video_id` ASC )
It contains a subquery, but maybe not in the sense you were thinking.
它包含一个子查询，但可能不是您所想的那样。
回答by davek
can't you just do this?:
你不能这样做吗？：
SELECT video_id, max(video_plays) AS max_video_plays
FROM `video_statistics` v_stat
GROUP BY v_stat.`video_id` ASC
There's an example here:
这里有一个例子：
http://dev.mysql.com/doc/refman/5.1/de/select.html
http://dev.mysql.com/doc/refman/5.1/de/select.html
SELECT user, MAX(salary) FROM users
GROUP BY user HAVING MAX(salary) > 10;
EDIT: second attempt, albeit using a subquery:
编辑：第二次尝试，尽管使用子查询：
select max(sum_video_plays)
from (
SELECT video_id, sum(video_plays) AS sum_video_plays
FROM `video_statistics` v_stat
GROUP BY v_stat.`video_id`
) x
Your outer query may well be selecting from a much smaller set, and (depending on your data distribution etc.) may be quite performant.
您的外部查询很可能是从一个更小的集合中进行选择，并且（取决于您的数据分布等）可能非常高效。
