mysql 分组求均值_MySQL 分组最值、分组均值、分组求和

数据表

假设有 student 表，数据如下

+----+-------+-------+----------+

| id | name | score | class_id |

+----+-------+-------+----------+

| 1 | 刘备1 | 90 | 1 |

| 2 | 刘备2 | 88 | 2 |

| 3 | 张飞1 | 70 | 1 |

| 4 | 张飞2 | 99 | 2 |

+----+-------+-------+----------+

问题 1：求每个班的最高分

解：select class_id, max(score) from student group by class_id;

+----------+------------+

| class_id | max(score) |

+----------+------------+

| 1 | 90 |

| 2 | 99 |

+----------+------------+

问题 2：求每个班的最低分

解：select class_id, min(score) from student group by class_id;

+----------+------------+

| class_id | min(score) |

+----------+------------+

| 1 | 70 |

| 2 | 88 |

+----------+------------+

问题 3：求每个班的平均分

解：select class_id, avg(score) from student group by class_id;

+----------+------------+

| class_id | avg(score) |

+----------+------------+

| 1 | 80.0000 |

| 2 | 93.5000 |

+----------+------------+

问题 4：求每个班的总分

解：select class_id, sum(score) from student group by class_id;

+----------+------------+

| class_id | sum(score) |

+----------+------------+

| 1 | 160 |

| 2 | 187 |

+----------+------------+