java8 多个字段分组_MySQL基础之分组查询

在MySQL 查询 语句中，允许使用 GROUP BY 子句对结果分组。

GROUP BY语法：

select 分组函数, 列(要求在group by 子句后面)from 表名【where 条件】group by 分组的列【order by 子句】
           

准备一个表，和一些数据。员工表，表名emp，包含姓名(ename)、工作(job)、工资(sal)、 部门编号(deptNo)等字段。

CREATE TABLE `emp` ( `id` int(255) NOT NULL AUTO_INCREMENT, `ename` varchar(255) DEFAULT NULL, `job` varchar(255) DEFAULT NULL, `sal` int(11) DEFAULT NULL, `deptNo` int(11) DEFAULT NULL, `hiredate` datetime NOT NULL, `bonus` int(11) DEFAULT NULL, `orderNo` varchar(255) DEFAULT NULL, PRIMARY KEY (`id`)) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8
           

准备好的一些测试数据

简单分组

假设有一个需求：查询每个部门的员工人数。

使用count函数可以查询个数，select count(*) from emp 将整个表看作一组，返回整个表的行数。此时要求查询每个部门的员工人数，意味着要在整个emp表再按部门分组，有几个部门就分几个小组，然后再统计部门的人数。

可以这样写：

mysql> select count(*),deptNo  from emp group by deptNo;+----------+--------+| count(*) | deptNo |+----------+--------+| 2 | 1 || 2 | 2 | | 2 | 3 | | 2 | 4 | | 2 | 5 | +----------+--------+
           

deptNo是部门编号，所以以此列作为分组的列。前面是员工数量，后面是部门编号。

再如，查询每个部门的平均工资

mysql> select avg(sal),deptNo from emp group by deptNo;+-----------+--------+| avg(sal) | deptNo |+-----------+--------+| 4000.0000 | 1 || 4100.0000 | 2 || 3250.0000 | 3 || 4000.0000 | 4 || 3500.0000 | 5 |+-----------+--------+
           

有条件的分组查询

1、比如第一个问题改一下，查询每个部门有奖金的员工人数(即bonus这列，其有两个值，null和非null)

此时可以这样查

mysql> select count(*),deptNo  from emp  where bonus is not null  group by deptNo;+----------+--------+| count(*) | deptNo |+----------+--------+| 1 | 1 || 1 | 2 || 1 | 3 || 1 | 4 || 1 | 5 |+----------+--------+
           

奖金是筛选条件，条件涉及的字段bonus是在emp表中，也就是原始表中，此时就可以在where中使用，并且在group by 之前。

2、再有一个问题，要求查询员工人数大于1的部门。

第一步，我们可以先查出每个部门的人数

第二步，根据第一步的结果上进行筛选哪个部门员工人数大于1

查询部门员工人数select count(*),deptNo from emp group by deptNo;
           

此时我们发现，根据原始表的字段，我们无法找出哪个部门的员工人数大于1，只能是根据分组之后的结果集进行筛选，此时添加条件不能在group by之前，否则会报错。

mysql> select count(*),deptNo  from emp  where count(*) > 1  group by deptNo;ERROR 1111 (HY000): Invalid use of group function
           

是group by分组之后再筛选，所以条件也在group by 子句后面，也不能再用where，而是用关键词having

mysql> select count(*),deptNo  from emp  group by deptNo  having count(*) > 1;+----------+--------+| count(*) | deptNo |+----------+--------+| 2 | 1 || 2 | 2 || 2 | 3 || 2 | 4 || 2 | 5 |+----------+--------+
           

筛选条件在分组前添加还是在分组后添加，可以看它能不能使用原始表中的列来确定。

分组前 原始表字段，在group by前面，用where关键词，分组后 分组的结果集，在group by后面，用having关键词。
           

如果用分组函数作条件，肯定是在group by后面。

优先使用分组前筛选。

按函数分组或者表达式分组

如按员工姓名的长度进行分组，查询每个长度的员工人数。

mysql> select count(*),length(ename)  from emp group by length(ename);+----------+---------------+| count(*) | length(ename) |+----------+---------------+| 4 | 4 || 1 | 5 || 3 | 6 || 1 | 7 || 1 | 8 |+----------+---------------+
           

按多个字段分组

如按部门、工作分组，查询员工数量

mysql> select count(*),deptNo,job  from emp  group by deptNo,job;+----------+--------+-------------+| count(*) | deptNo | job |+----------+--------+-------------+| 1 | 1 | accountant || 1 | 1 | auditor || 1 | 2 | cashier || 1 | 2 | operator || 1 | 3 | engineer || 1 | 3 | secretary || 1 | 4 | buyer || 1 | 4 | electrician || 1 | 5 | interpreter || 1 | 5 | janitor |+----------+--------+-------------+
           

有多个字段，加在group by后面用逗号分隔即可，两者值相同的字段才会分到一组中。字段在分组的顺序随意，没有要求。

添加排序

如查询每个部门的平均工资，按照降序排序

mysql> select avg(sal),deptNo  from emp  group by deptNo  order by avg(sal) desc;+-----------+--------+| avg(sal) | deptNo |+-----------+--------+| 4100.0000 | 2 || 4000.0000 | 4 || 4000.0000 | 1 || 3500.0000 | 5 || 3250.0000 | 3 |+-----------+--------+
           

如果没有limit，order by 子句一定在查询语句的最后。