聚合函数的进阶应用

• 1.什么是聚合函数
  聚合函数aggregation function又称为组函数。 认情况下 聚合函数会对当前所在表当做一个组进行统计。

• 2.聚合函数的特点

  • 2.1每个组函数接收一个参数（字段名或者表达式） 统计结果中默认忽略字段为NULL的记录
  • 2.2要想列值为NULL的行也参与组函数的计算，必须使用IFNULL函数对NULL值做转换。

• 3.不允许出现嵌套 比如sum(max(xx))
  常用聚合函数min(),max(),count(),sum(),avg()等常和group by结合使用。
  聚合函数(常用于GROUP BY从句的SELECT查询中)
  AVG(col)返回指定列的平均值
  COUNT(col)返回指定列中非NULL值的个数
  MIN(col)返回指定列的最小值
  MAX(col)返回指定列的最大值
  SUM(col)返回指定列的所有值之和
  GROUP_CONCAT(col) 返回由属于一组的列值连接组合而成的结果

• 4.举个例子，存在两张表，一张学生信息表（Student），一张学生成绩表（SC）
  先建好这两张表

//学生信息表
create table Student(SID varchar(10),Sname nvarchar(10),Sage datetime,Ssex nvarchar(10));
//学生成绩表
create table SC(SID varchar(10),CID varchar(10),score decimal(18,1));

再添加点数据来测试

insert into Student values('01' , '张三' , '1990-01-01' , '男');
insert into Student values('02' , '李四' , '1990-12-21' , '男');
insert into Student values('03' , '王五' , '1990-05-20' , '男');
insert into Student values('04' , '周六' , '1990-08-06' , '男');
insert into Student values('05' , '静静' , '1991-12-01' , '女');
insert into Student values('06' , '蓝兰' , '1992-03-01' , '女');
insert into Student values('07' , '皓镧' , '1990-01-20' , '女');
insert into SC values('01' , '01' , 80);
insert into SC values('01' , '02' , 90);
insert into SC values('02' , '01' , 70);
insert into SC values('02' , '02' , 60);
insert into SC values('03' , '01' , 80);
insert into SC values('03' , '02' , 80);
insert into SC values('04' , '01' , 50);
insert into SC values('04' , '02' , 30);
insert into SC values('05' , '01' , 76);
insert into SC values('05' , '02' , 87);
insert into SC values('06' , '01' , 31);
insert into SC values('06' , '03' , 34);
insert into SC values('07' , '02' , 89);
insert into SC values('07' , '03' , 98);

例如count()函数，传入'*'返回所有行的统计结果，传入字段，根据where条件的执行结果返回对应的统计结果，其余几个函数也是如此

mysql> select count(*) from student;
+----------+
| count(*) |
+----------+
|        7 |
+----------+

mysql> select count(Ssex) from student where Ssex='女';
+-------------+
| count(Ssex) |
+-------------+
|           3 |
+-------------+

聚合函数和分组一起使用group by +having....
例：查询平均成绩大于等于60分的同学的学生编号和学生姓名和平均成绩

mysql> select a.SId,a.Sname,cast(avg(b.score) as decimal(18,2)) avg_score from student a,SC b where a.SId=b.SId group by a.SId,a.Sname having cast(avg(b.score) as decimal(18,2)) >= 60;
+------+--------+-----------+
| SId  | Sname  | avg_score |
+------+--------+-----------+
| 01   | 张三   |     85.00 |
| 02   | 李四   |     65.00 |
| 03   | 王五   |     80.00 |
| 05   | 静静   |     81.50 |
| 07   | 皓镧   |     93.50 |
+------+--------+-----------+
5 rows in set (0.00 sec)

分析一下，首先执行的顺序是先select语句，然后是where，接着执行group by 分组，分组完成后才执行avg()函数，最后经过having过滤输出。也就是说，在我们进行分组之前，都没有对相同的SID字段的对应分数取平均值。只要理解了这个sql运行的顺序，就不难使用聚合函数了。