mysql 按关键字分组查询（MySQL基础之分组查询）

mysql 按关键字分组查询（MySQL基础之分组查询）mysql> select count(*) deptNo from emp group by deptNo; ---------- -------- | count(*) | deptNo | ---------- -------- | 2 | 1 | | 2 | 2 | | 2 | 3 | | 2 | 4 | | 2 | 5 | ---------- -------- deptNo是部门编号，所以以此列作为分组的列。前面是员工数量，后面是部门编号。可以这样写：简单分组假设有一个需求：查询每个部门的员工人数。使用count函数可以查询个数，select count(*) from emp 将整个表看作一组，返回整个表的行数。此时要求查询每个部门的员工人数，意味着要在整个emp表再按部门分组，有几个部门就分几个小组，然后再统计部门的人数。

在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 子句一定在查询语句的最后。