多行函数

定义：作用于多行数据，最终返回一行数据的函数。 下面是mysql中常用的多行函数，也可以自定义函数；一般与group by分组查询关联使用。

avg(),min(),max(),sum(),count()  #均值，最小值，最大值，求和，行数

stddev(),variance() #标准差，方差

group by分组汇总统计

group by按指定列将数据分成若干组，然后对组内数据进行多行函数统计。 语法：select 查询字段 from tablename group by 实施分组的列名； 将数据按照分组列名分组，针对每个分组执行查询条件操作，一般是多行函数。直接出现的查询字段必须在执行分组的列内，要查询未进行分组的列则必须对这些列使用多行函数操作，否则sql会报语法错误。  

示例：

按照单列分组

#按照班级分组，查询每个班的平均  最低  最高成绩

mysql> select cno,AVG(degree) from score group by cno;
+-------+-------------+
| cno | AVG(degree) |
+-------+-------------+
| 3-105 | 81.5000 |
| 3-245 | 76.3333 |
| 6-166 | 81.6667 |
+-------+-------------+
3 rows in set (0.00 sec)

mysql> select cno,min(degree) from score group by cno;
+-------+-------------+
| cno | min(degree) |
+-------+-------------+
| 3-105 | 64 |
| 3-245 | 68 |
| 6-166 | 79 |
+-------+-------------+
3 rows in set (0.01 sec)

mysql> select cno,max(degree) from score group by cno;
+-------+-------------+
| cno | max(degree) |
+-------+-------------+
| 3-105 | 92 |
| 3-245 | 86 |
| 6-166 | 85 |
+-------+-------------+
3 rows in set (0.00 sec)

 

#按照班级分组查询每个班成绩的累加值   标准差  方差  一共多少条成绩记录

mysql> select cno,sum(degree) from score group by cno;
+-------+-------------+
| cno | sum(degree) |
+-------+-------------+
| 3-105 | 489 |
| 3-245 | 229 |
| 6-166 | 245 |
+-------+-------------+

mysql> select cno,stddev(degree) from score group by cno;
+-------+--------------------+
| cno | stddev(degree) |
+-------+--------------------+
| 3-105 | 9.928914005737653 |
| 3-245 | 7.4087035902976215 |
| 6-166 | 2.4944382578492945 |
+-------+--------------------+
3 rows in set (0.00 sec)

mysql> select cno,variance(degree) from score group by cno;
+-------+--------------------+
| cno | variance(degree) |
+-------+--------------------+
| 3-105 | 98.58333333333331 |
| 3-245 | 54.888888888888864 |
| 6-166 | 6.222222222222224 |
+-------+--------------------+

mysql> select cno,count(degree) from score group by cno;
+-------+---------------+
| cno | count(degree) |
+-------+---------------+
| 3-105 | 6 |
| 3-245 | 3 |
| 6-166 | 3 |
+-------+---------------+

按照多列分组：

如果group by使用多列进行分组的话是对这列组合的结果进行去重，对分组列重复的其他列使用多行函数操作

#按照 cno,sno分组，查询degree的最大值

mysql> select sno,cno,max(degree) from score group by cno,sno;
+-----+-------+-------------+
| sno | cno | max(degree) |
+-----+-------+-------------+
| 103 | 3-105 | 92 |
| 105 | 3-105 | 91 |
| 109 | 3-105 | 78 |
| 103 | 3-245 | 86 |
| 105 | 3-245 | 75 |
| 109 | 3-245 | 68 |
| 103 | 6-166 | 85 |
| 105 | 6-166 | 79 |
| 109 | 6-166 | 81 |
+-----+-------+-------------+

 过滤分组函数having

having可以对多行行数的结果执行条件表达式，达到过滤数据的目的；类似于where关键字，where对单行生效having对多行结果生效

#按照班级分组查询平均成绩大于80的班

mysql> select cno,avg(degree) from score group by cno having avg(degree) > 80;
+-------+-------------+
| cno | avg(degree) |
+-------+-------------+
| 3-105 | 81.5000 |
| 6-166 | 81.6667 |

分组前进行数据过滤

#按照班级统计每个班成绩大于80分的平均成绩

mysql> select cno,avg(degree) from score where degree > 80 group by cno;
+-------+-------------+
| cno | avg(degree) |
+-------+-------------+
| 3-105 | 90.3333 |
| 3-245 | 86.0000 |
| 6-166 | 83.0000 |