Mysql基础（7）分组查询（group by子句）_mysql子查询group by

group by子句按照指定字段对数据表进行分组，使指定字段相同的数据行为一组。在此基础上，可以使用分组函数，对这些数据组进行组内统计，譬如统计某一个组的最大值、最小值等。group by关键字后的字段具有两个身份：其一是一般数据表字段（列名），其二是组的逻辑标识符（类似组名变量，代表一组数据）。

当字段充当组名变量时，分组函数和字段都包含’多‘的逻辑含义，进而可以逻辑对齐，因此两者可以同时出现在查询列表中。这即是分组函数只能和group by后的字段同时出现在查询列表中的原因。

其基本语法为

select 分组函数，列（要求出现在group by后面）
from 表名
[where字句]
group by 列名
[order by 字句]
1
2
3
4
5

基础分组查询

在写sql时，分析查询目标，找到识别词，然后转换到sql语境中，使用对应的sql子句。譬如 一般出现 ’每个XXX的统计维度‘ 时，这就涉及了一个分组统计的概念，可以使用group by子句。

基础分组查询是指可是在原始表的数据行中筛选，其的关键是分析查询目标，找到分组的依据，即所谓的组名，然后按照语法编写sql语句。

实例

#查询每个部门的最高工资
SELECT MAX(salary),job_id as 组名 FROM employees
 GROUP BY job_id  //前一个job_id可以理解为组名变量，即查询结果中 job_id列表示组名列。
# where子句在原始表的数据行中过滤commission_pct不为null
SELECT MAX(salary),manager_id FROM employees 
 WHERE `commission_pct` IS NOT NULL GROUP BY manager_id
1
2
3
4
5
6

复杂分组查询（having子句）

复杂分组查询是在分组之后，存在过滤条件，此时需要使用having子句。在 SQL 中增加 HAVING 子句原因是，WHERE 关键字无法与聚合函数一起使用。

HAVING 子句可以让我们筛选分组后的各组数据。其基本步骤如下：

1. 先进行基础分组查询
2. 添加分组前筛选条件【where子句】，依据：在原数据表可查询
3. 添加分组后筛选条件【having子句】，依据：只能在分组后的中间结果表中查到，多是分组函数结果

实例 查询领导编号 >102的每个领导手下员工的最低工资>5000的领导编号和最低工资。

#步骤1 确定查询目标、分组点 。存在 ‘每个’、‘最低’字样，则使用group by子句和分组函数
SELECT manager_id,MIN(salary) mm FROM employees GROUP BY manager_id 
#步骤2 添加where子句。领导编号在原数据表可查到，因此使用where子句
SELECT manager_id,MIN(salary) mm FROM employees WHERE manager_id > 102  GROUP BY manager_id 
#步骤3 添加havin子句 。最低工资 只能在分组后查询，因此使用having子句
SELECT manager_id,MIN(salary) mm FROM employees WHERE manager_id > 102 GROUP BY manager_id HAVING mm>5000 
1
2
3
4
5
6

按函数分组

函数j结果也可作为分组依据，即group by func()

实例 按员工姓名长度分组，查询每组的员工个数>5的员工个数

SELECT COUNT(*) cc,LENGTH(last_name) FROM employees GROUP BY LENGTH(last_name) HAVING cc >5
1

按多个字段分组

使用group by 字段1，字段2，字段N实现按多字段分组，mysql会将多个字段值都相等的数据行放置为一组。

假设按字段1，字段2分组，若第X行的字段1、字段2的值同第Y行的字段1、字段2的值相等，那么这两行数据会被分为一组。

实例 查询每个部门中每个工种的员工的平均工资

SELECT AVG(salary) ,department_id,job_id FROM employees GROUP BY department_id,job_id
1

对比

两个经验

• 若分组函数作为筛选条件，其筛选条件一定是放在having子句中。

• 优先考虑使用where子句，即分组前筛选。

  当字段在原始表中， where子句和having子句都可使用，这种情况优先使用where子句，提高性能。

  # 以下两种sql作用一致，优先使用第一种。
  SELECT manager_id,MIN(salary) mm FROM employees WHERE manager_id > 102  GROUP BY manager_id 
  SELECT manager_id,MIN(salary) mm FROM employees  GROUP BY manager_id HAVING manager_id > 102 
  1
  2
  3