MySQL - 获取组的 sum() 分组 max()

发布于 2024-11-18 00:48:13 字数 884 浏览 4 评论 0原文

我有如下表结构。每行都是一场游戏，每个人每个月可以玩很多次或不玩。

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 |

我试图得到每个人每个月最好成绩的总和。上面的结果应该是：

 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 之外的分数。

现在我正在阅读有关分组最大值的内容，并且仍然尝试获得预期的结果。任何帮助

原文

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

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

余生共白头 2024-11-25 00:48:13

子查询：

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

现在子查询按人和月份（日期）分组，因此它将每个月返回一行。

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.

回复收藏 0 原文

得不到的就毁灭 2024-11-25 00:48:13

Select t.person,Max(t.Score) from 
(
Select person,Sum(Score) as Score,Month('Date') as [Month]  From tabgames
WHERE MONTH(`date`) >= 1 AND MONTH(`date`) <= 6 
GROUP BY PERSON,Month

) AS t

Select t.person,Max(t.Score) from 
(
Select person,Sum(Score) as Score,Month('Date') as [Month]  From tabgames
WHERE MONTH(`date`) >= 1 AND MONTH(`date`) <= 6 
GROUP BY PERSON,Month

) AS t

回复收藏 0 原文

~没有更多了~