MySQL 查询 GROUP, COUNT

Question

我对 MySQL GROUP AND COUNT 有疑问，它们并不像我想的那样工作。

Table1
ID | group_
1    1
2    1 
3    2  

Table2
ID | score
1    100
1    80  
2    50  
3    50 

OUTPUT
group_ | group_avg_score | group_num
1        70                2
2        50                1

这是我的 MySQL 查询和错误输出。请注意，对于组 1 avg_score 应该是 ID1 avg_score 90 和 ID2 avg_score 50，(90+50)/2=70

SELECT  A.group_, ROUND(AVG(B.score),2) as group_avg_score, COUNT(*) as group_num
FROM Table1 A, Table2 B
WHERE A.ID=B.ID
GROUP BY group_

OUTPUT
group_ | group_avg_score | group_num
1        76.67             3
2        50                1

我该如何解决？

Answer 1

从您的解释中，我可以看出您想要平均值的平均值。这可以通过派生表来解决(FROM 子句中的嵌套选择)。首先计算“内部”平均值:

SELECT ID, AVG(score) AS score
FROM Table2
GROUP BY ID

然后，按如下方式嵌套该查询:

SELECT Table1.group_, AVG(Table2.score) AS group_avg_score, COUNT(*) AS group_num
FROM Table1
JOIN (
  SELECT ID, AVG(score) AS score
  FROM Table2
  GROUP BY ID
) Table2
ON Table1.ID = Table2.ID
GROUP BY Table1.group_

SQLFiddle