MySQL-计算AVG以获得MAX值的子集 [英] MySQL - working out the AVG for a subset of MAX values

问题描述

我有一个表格，其中包含日期，名称，分数"列.

I have a table with columns 'Date, Name, Score'.

在将它们取平均值给我之前，我希望获得具有共同值(例如，相同的日期甚至名称)的行的MAX(Score)，例如:

I wish to get the MAX(Score) for rows which share a common value (for e.g. the same date or even name), before averaging them to give me a figure, for example:

----日期----- | -名称-|分数
2010-10-10 |约翰·史密斯| 86
2010-06-05 |泰迪·琼斯| 71
2010-10-10 |约翰·史密斯| 52
2010-06-05 |泰迪·琼斯| 68
2010-08-08 |乔·博格斯| 79
2010-10-10 |约翰·史密斯| 46

---- Date -----| -- Name -- | Score
2010-10-10 | John Smith | 86
2010-06-05 | Tedi Jones | 71
2010-10-10 | John Smith | 52
2010-06-05 | Tedi Jones | 68
2010-08-08 | Joe Bloggs | 79
2010-10-10 | John Smith | 46

因此，在上面做一个MAX(Score)会给我86.但是，我想要的是以下内容:

So doing a MAX(Score) on the above would give me 86. However, what I'd like is the following:

MAX(Score)为我提供值86(日期10-10的最大值)，79(日期08-08的最大值)和71(日期06-05的最大值)，然后我可以取平均值得到78.67.我希望不必借助临时表就可以做到这一点?

MAX(Score) to give me the values 86 (MAX for date 10-10), 79 (MAX for date 08-08) and 71 (MAX for date 06-05) which I can then average to get 78.67. I'm hoping this is possible without having to resort to temp tables?

感谢所有回复，谢谢.

推荐答案

每日最大值的总平均值:

Total average of daily maximal values:

SELECT AVG(dailyMax) AS avgOfDailyMax
FROM (SELECT Date, MAX(Score) AS dailyMax FROM MyTable GROUP BY Date) as DailyMaxTable 

和每日最大值:

SELECT Date, MAX(Score) AS dailyMax
FROM MyTable
GROUP BY Date

这篇关于MySQL-计算AVG以获得MAX值的子集的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！