mysql 聚合函数取值_mysql聚合函数的使用

常用的聚合函数有 sum(求和) 、count(*) 记录数 、max(最大值)、min(最小值)

group by 关键字表示要进行分类聚合的字段，比如要按照班级分类进行学生人数统计，班级就应该放在group by 的后面。

with rollup是可选语法，表明是否对分类聚合后的结果进行汇总。having关键字表示对分类后的结果再进行条件筛选

having和where的区别在于，having是对聚合后的结果进行条件的筛选，而where是在聚合前就对记录进行过滤，如果逻辑允许，我们尽可能用where先过滤记录，这样因为结果集小，将对聚合的效率大大提高。

我们将要使用的student表

mysql> select * from student;

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

| id | name | score | classid |

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

| 1 | 张三 | 99 | 3 |

| 2 | 李四 | 60 | 2 |

| 3 | 王五 | 88 | 2 |

| 4 | 赵6 | 22 | 1 |

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

4 rows in set (0.00 sec)

查看student表中总人数

mysql> select count(1) from student;

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

| count(1) |

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

| 4 |

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

1 row in set (0.00 sec)

统计各个班级的人数

mysql> select classid , count(1) from student group by classid;

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

| classid | count(1) |

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

| 1 | 1 |

| 2 | 2 |

| 3 | 1 |

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

3 rows in set (0.00 sec)

既要统计各个班级的人数，又要统计总人数

mysql> select classid , count(1) from student group by classid with rollup;

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

| classid | count(1) |

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

| 1 | 1 |

| 2 | 2 |

| 3 | 1 |

| NULL | 4 |

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

4 rows in set (0.00 sec)

统计人数大于1的班级

mysql> select classid,count(1) from student group by classid having count(1)>1;

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

| classid | count(1) |

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

| 2 | 2 |

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

1 row in set (0.00 sec)

统计学生总分数，最高和最低分数

mysql> select sum(score),max(score),min(score) from student;

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

| sum(score) | max(score) | min(score) |

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

| 269 | 99 | 22 |

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

1 row in set (0.00 sec)