MySQL 如何在MySql中求和和除法
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/11193180/
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
How to sum and divide in MySql
提问by
Alright, so I have a user table and would like to get the max value for the user with the highest amount of points divided by a score. Below is a roughidea of what I'm looking for:
好的，所以我有一个用户表，并希望获得分数最高的用户的最大值除以分数。以下是我正在寻找的内容的粗略想法：
SELECT MAX(SUM(points)/SUM(score)) FROM users
I'm not interested in adding up both columns and dividing, rather I'm interested in dividing the points and score for each user and retrieve the highest value out of the lot.
我对将两列相加和除法不感兴趣，而是对将每个用户的点数和分数相除并从中检索最高值感兴趣。
回答by FrustratedWithFormsDesigner
Maybe you could do this with a subquery:
也许你可以用一个子查询来做到这一点：
Select max(points_over_Score)
from
(Select points/score AS points_over_score
from users);
And as thesunneversets mentioned in a comment, that can probably be shortened to
正如评论中提到的 thesunneversets ，这可能可以缩短为
SELECT MAX(points/score) FROM users;
You're written description of what you're trying to do doesn't make it clear why your example has SUMin it, so I didn't use it.
你对你想要做的事情的书面描述并没有说明为什么你的例子中有SUM它，所以我没有使用它。
Also, code questions like this one are more appropriate for stackoverflow.com. You can flag your own question to ask a moderator to migrate it.
此外，像这样的代码问题更适合stackoverflow.com。您可以标记您自己的问题以要求版主迁移它。
回答by Tom
FrustratedWithFormsDesigner's answer won't work if you need to sum up all of the points, then all of the scores, and then divide the two. That answer divides each point by the score, then returns the highest answer.
如果您需要总结所有点，然后总结所有分数，然后将两者相除，FrustratedWithFormsDesigner 的答案将不起作用。该答案将每个点除以分数，然后返回最高答案。
This should work:
这应该有效：
SELECT MAX(sum_points / sum_score) from
(SELECT SUM(points) as sum_points, SUM(score) as sum_score FROM users)
BTW, this returns an "unusual" percentage. You probably want to divide score by points. For example, if you get 70 out of 100, that is 70%, or score (70) / points (100).
顺便说一句，这会返回一个“不寻常”的百分比。您可能想将分数除以分数。例如，如果您得到 100 分中的 70 分，即 70%，或得分 (70) / 分 (100)。
回答by oldgregg
I'm not sure if this is due to the age of this post or version of MySQL at the time, but when I tried the above solutions the following error was thrown:
我不确定这是否是由于这篇文章的年龄或当时的 MySQL 版本，但是当我尝试上述解决方案时，抛出了以下错误：
ERROR 1248 (42000) : Every derived table must have its own alias
ERROR 1248 (42000) : 每个派生表都必须有自己的别名
I was able to resolve this using the following:
我能够使用以下方法解决此问题：
SELECT MAX(sum_points / sum_score) from (SELECT SUM(points) as sum_points, SUM(score) as sum_score FROM users) as max_value;
SELECT MAX(sum_points / sum_score) from (SELECT SUM(points) as sum_points, SUM(score) as sum_score FROM users) as max_value;
