表记录的查询（二）

表记录的查询（二）

group by 分组查询：按分组条件分组后每一组只显示第一条记录

使用group by报错

ERROR 1055 (42000): Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'test.em.id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

错误原因

• mysql默认开启了 only_full_group_by
• 查询sql_model的值 select @@global.sql_mode;
• ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
• 去掉ONLY_FULL_GROUP_BY，重新设置值:
• set @@global.sql_mode ='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';

• 按照name进行分组查询： select name,dp from em group by name;,name中有两个Tim,但是分组查询后仅显示一个

+--------+-------+
| name   | dp    |
+--------+-------+
| Tim    | NULL  |
| Linzy  | 2345  |
| Jessic | 2345  |
| Miz    | sales |
| Sasa   | sales |
| John   | NULL  |
+--------+-------+

group by分组,其后可以接多个列名，也可以跟having子句，对group by的结果进行筛选

• 对表内数据按部门分组后，显示每一个部门的bouns总和,并按照bouns总和的降序排列select DP, sum(bouns) as sumb from em group by DP order by sumb desc;

+-------+------+
| DP    | sumb |
+-------+------+
| sales | 3400 |
| NULL  |  750 |
| 2345  |  500 |
| IT    |  400 |
+-------+------+

• 对表内数据按部门分组后，显示部门的bouns最小值小于400的部门,并按照bouns总和的降序排列select DP, min(bouns) as minb from em group by dp having minb < 400 order by minb desc;

+------+------+
| DP   | minb |
+------+------+
| NULL |  350 |
| 2345 |  200 |
+------+------+

having和where都可以对查询结果进行过滤，差别在于：

• where语句只能用在分组以前的筛选，having可以用在筛选之后
• 使用where语句的地方都可以使用having进行替换
• having中可以使用聚合函数sum\max\min等，where中不可以使用聚合函数

• 对表内数据按照部门分组且部门名称不为null，显示bouns最大值小于800的部门，并按照bouns最大值的降序排列select dp, max(bouns) as maxb from em where dp!="null" group by dp having maxb < 800 order by maxb desc;

+------+------+
| dp   | maxb |
+------+------+
| IT   |  400 |
| 2345 |  300 |
+------+------+