mysql - SQL 查找每个地区每个学生交付的平均公斤数

Question

我有三个表

school
| id      | area       
|       1 |      area1
|       2 |      area1 
|       3 |      area1 
|       4 |      area2 

class
| id      | school     | students  |
|       1 |          1 |     2     |
|       2 |          2 |     28    |
|       3 |          3 |     22    |
|       4 |          4 |     4     |

deliveries
| id      | kg         | classid   |
|       1 |      120   |     1     |
|       2 |      80    |     1     |
|       3 |      20    |     1     |
|       4 |      200   |     2     |
|       5 |      150   |     3     |
|       6 |      14    |     2     |

我需要总结每个地区每个学生交付的平均公斤数。

对于 area1，应该等于 (120+80+20+200+150+14)/(2+28+22) = 11.23 但我不知道如何编写该查询。我想我必须先使用某种子查询对 area1 (52) 中的学生求和，然后再对交付的公斤数求和并除以学生？

Answer 1

是的，您需要使用子查询对学生求和，因为如果您使用普通连接，则 class 记录将被复制，因为 class 中有多个记录与 deliveries 表。

子查询将计算每个区域的学生数。外部查询计算每个区域的交付量。在表达式中使用 max(t.area_students) 是因为每个交付记录都将包含与该交付相关联的学生总数。

select school.area, sum(deliveries.kg)/max(t.area_students) as kg_per_student
from school
inner join class on school.id=class.school
inner join deliveries on class.id=deliveries.classid
inner join
    (select area, sum(students) as area_students
     from school
     inner join class on school.id=class.school
     group by school.area) t on school.area=t.area
group by school.area