Mysql分组以及聚合函数

• group by

group by语法可以根据给定字段对查询结果进行分组统计，相同属性的数据为一个组。通常，在每组中通过聚合函数来可以计算组中最大，最小等。

如果group by带有having,则只有满足having后面的条件的组才能输出。

注意： having 必须在group by之后。

• 与分组相关的聚合函数

  count() 返回某个字段的值的行数

  max() 求最大某字段最大的值 
  min() 求某字段最小值 
  avg() 返回某字段平均值 
  sum() 求某字段总数

  group_concat() 将分组的结果拼接成字符串

• group by分组SQL语法

  select 字段 from 表名 where where条件 group by 分组条件 having having条件

  order by 字段 asc/desc limit offset, row

  注意：

  1. GROUP BY必须出现在WHERE 之后，ORDER BY 之前。

  2. 除聚集计算语句外，SELECT语句中的每个列都必须在GROUP BY中给出

     //错误，由于name字段没有写到group by之后
     1
     2

     select count(id),name,sex from user group by sex;

     //正确写法 
     select count(id),name,sex from user group by sex,name;

  备注：

  group by 用于根据字段进行分组

  having 指定分组的搜索条件,对分组的结果做进一步的处理

  limit 显示查询出来的数据条数

例子

1. 某个员工信息表(staff)结构和数据如下： 
   id name dept salary edlevel hiredate 
   1 张三 开发部 2000 3 2009-10-11 
   2 李四 开发部 2500 3 2009-10-01 
   3 王五 设计部 2600 5 2010-10-02 
   4 王六 设计部 2300 4 2010-10-03 
   5 马七 设计部 2100 4 2010-10-06 
   6 赵八 销售部 3000 5 2010-10-05 
   7 钱九 销售部 3100 7 2010-10-07 
   8 孙十 销售部 3500 7 2010-10-06

//staff建表语句

create table staff(    id int not null primary key auto_increment,    name varchar(200) not null,    dept varchar(50) not null comment '部门',    salary float(15,2) comment '薪水',    edlevel tinyint not null comment '等级',    hiredate date not null comment '入职时间')engine=innodb default charset=utf8;insert into staff(name,dept,salary,edlevel,hiredate) values('张三','开发部',2000,3,'2009-10-11');insert into staff(name,dept,salary,edlevel,hiredate) values('李四','开发部',2500,3,'2009-10-01');insert into staff(name,dept,salary,edlevel,hiredate) values('王五','设计部',2600,5,'2010-10-02');insert into staff(name,dept,salary,edlevel,hiredate) values('王六','设计部',2300,4,'2010-10-03');insert into staff(name,dept,salary,edlevel,hiredate) values('马七','设计部',2100,4,'2010-10-06');insert into staff(name,dept,salary,edlevel,hiredate) values('赵八','销售部',3000,5,'2010-10-05');insert into staff(name,dept,salary,edlevel,hiredate) values('钱九','销售部',3100,7,'2010-10-07');insert into staff(name,dept,salary,edlevel,hiredate) values('孙十','销售部',3500,7,'2010-10-06');
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

聚合函数的简单使用

1. 求公司总人数

   select count(id) from staff;
   1
   2

2. 求公司薪水支出

   select sum(salary) from staff;
   1
   2

3. 求公司最高薪水

   select max(salary) from staff;
   1
   2

4. 求公司最低薪水

   select min(salary) from staff;
   1
   2

5. 求公司平均薪水

   select avg(salary) from staff;
   1
   2

分组与聚合函数结合使用例子

例如，统计出各个部门的员工人数

select count(*) from staff group by dept;
1
2

• group_concat函数与group by结合使用例子

求： 得到每个部门的员工的名字

select dept,group_concat(name) from staff group by dept;
1
2

例如，列出每个部门最高薪水的结果，sql语句如下：

SELECT dept, MAX(SALARY) FROM staff GROUP BY dept
1
2

查询结果如下： 
dept MAXIMUM 
开发部 2500 
设计部 2600 
销售部 3500

1. 查询每个部门的总薪水数

   select dept, sum(salary) as total from staff group by dept;
   1
   2

   查询结果如下：

   dept total 
   开发部 4500 
   设计部 7000 
   销售部 9600

2. 将where字句与group by 子句一起使用

注意： 必须在group by之前指定where

例子： 查询公司2010年之后入职的各个部门每个级别里的最高薪水

    SELECT DEPT, EDLEVEL, MAX( SALARY ) AS MAXIMUMFROM staffWHERE HIREDATE > '2010-01-01'GROUP BY DEPT, EDLEVEL;
1
2
3
4
5

查询结果如下： 
DEPT EDLEVEL MAXIMUM 
设计部 4 2300 
设计部 5 2600 
销售部 5 3000 
销售部 7 3500

1. 在GROUP BY子句之后使用HAVING子句

可应用限定条件进行分组，以便系统仅对满足条件的组返回结果。为此，在GROUP BY子句后面包含一个HAVING子句。HAVING子句可包含一个或多个用AND和OR连接的谓词。

例如：寻找雇员数超过2个的部门的最高和最低薪水：

SELECT DEPT, MAX( salary ) AS MAXIMUM, MIN( salary ) AS MINIMUMFROM staffGROUP BY DEPTHAVING COUNT( * ) >2;
1
2
3
4
5

查询结果如下： 
DEPT MAXIMUM MINIMUM 
设计部 2600 2100 
销售部 3500 3000

例如：寻找雇员平均工资大于3000的部门的最高和最低薪水：

select max(salary),min(salary),dept from staff group by dept having avg(salary) > 3000; 
1
2

查询结果如下： 
DEPT MAXIMUM MINIMUM 
销售部 3500 3000