如何在MySql中求和

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 rough idea 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.

我没有兴趣将两个列和分割相加,而是我有兴趣将每个用户的得分和分数分开并从中检索最高值。

3 个解决方案

#1

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

正如评论中提到的那些无人问津,这可能会缩短为

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 SUM in it, so I didn't use it.

你写的关于你想要做的事情的描述并不清楚为什么你的例子中有SUM,所以我没有使用它。

Also, code questions like this one are more appropriate for *.com. You can flag your own question to ask a moderator to migrate it.

此外,像这样的代码问题更适合*.com。您可以标记自己的问题,以请主持人进行迁移。

#2

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分)。

#3

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

错误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;

#1