MySQL - 获取sum（）分组的max（） [英] MySQL - get sum() grouped max() of group

问题描述

我有下面的表格结构。每行是一个玩过的游戏，每个人每个月可以玩很多次或没有玩过。

 
 id |人|分数|日期| 
 ------------------------------------ 
 1 | 32 | 444 | 2011-05 | 
 2 | 65 | 528 | 2011-05 | 
 3 | 77 | 455 | 2011-05 | 
 4 | 32 | 266 | 2011-06 | 
 5 | 77 | 100 | 2011-06 | 
 6 | 77 | 457 | 2011-06 | 
 7 | 77 | 457 | 2011-06 | 
 8 | 65 | 999 | 2011-07 | 
 9 | 32 | 222 | 2011-07 | 
 

我试图为每个人获得每个月最佳分数的总和。上面的结果应该是：

 
 person | SUM（ofbestofeachmonth）
 --------------------------------- 
 32 | 932 
 65 | 1527 
 77 | 912 
 

我知道如何在每个月或某个范围内获取每位用户的最佳分数

 
 SELECT person，date，MAX（score）FROM tabgames WHERE MONTH（date）= 6 GROUP BY person HAVING（score> 0）
 
 
 
 
因为我最终需要每个季度的年度产出，现在我正在为每个月和最新的MySQL数据提供最好的产品。
 
 
 
现在我正在阅读关于组明智的最大值，并仍尝试获得令人惊叹的结果。任何帮助 
 
解决方案
子查询： 
 
 
  SELECT person，SUM（best）
 FROM 
（SELECT person，MAX（score）as best 
 FROM tabgames 
 WHERE MONTH（`date`）> = 1 AND MONTH（'date`）<= 6 
 GROUP BY person，MONTH（`date`））as bests 
 GROUP BY person 
  
现在子查询按人物和MONTH（日期）分组，所以它会为每个月返回一行。
 
I have table structure like below. Each row is one played game, each person can play many or none times in each month.
id  |  person  |  score  |   date  |
------------------------------------
1   |    32    |  444    | 2011-05 |
2   |    65    |  528    | 2011-05 |
3   |    77    |  455    | 2011-05 |
4   |    32    |  266    | 2011-06 |
5   |    77    |  100    | 2011-06 |
6   |    77    |  457    | 2011-06 |
7   |    77    |  457    | 2011-06 |
8   |    65    |  999    | 2011-07 |
9   |    32    |  222    | 2011-07 |
I am trying to get for each person sum of its best score in each month. S result of above should be:
 person  | SUM(ofbestofeachmonth)
---------------------------------
  32     |  932
  65     |  1527
  77     |  912
I know how to fetch the bests scores per userin month or some range
SELECT person, date, MAX(score) FROM tabgames WHERE MONTH(date) = 6 GROUP BY person HAVING (score>0)
Because i need in the end output per quarter of year, now i am fetching best for each month and outside the MySQL i am adding.

Now i am reading about group-wise max and still try to get excpected results. Any help
解决方案
Subqueries:
SELECT person, SUM(best)
FROM
    (SELECT person, MAX(score) as best
    FROM tabgames
    WHERE MONTH(`date`) >= 1 AND MONTH(`date`) <= 6 
    GROUP BY person, MONTH(`date`)) as bests
GROUP BY person
Now the subquery is grouped by person and MONTH(date), so it will return a row for each month.

                        
这篇关于MySQL  - 获取sum（）分组的max（）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！