mysql分组_MySQL 分组查询-GROUP BY

分组查询语法

select 分组函数，列

from 表名

【where】 筛选条件 -- where的位置可以放在group by的前面或者后面，两者有不同含义

group by 分组的列表

【having】筛选条件

【order by】 --如果使用到排序，order by语句基本在最后

where的使用特点：1，

数据源 位置 关键字

分组前筛选 来源于‘from’表中 group by前 where

分组后筛选 来源于分组函数计算过结果集 group by后 having

2，group by 子句支持单个字段，多个字段(字段之间用，隔开)，表达式或者函数

SELECT MAX(salary),job_id

FROM employees

GROUP BY job_id;

SELECT COUNT(*),location_id

FROM departments

GROUP BY location_id;

添加筛选条件

SELECT AVG(salary),department_id

FROM employees

WHERE email LIKE '%a%'

GROUP BY department_id;

SELECT MAX(salary),manager_id

FROM employees

WHERE commission_pct IS NOT NULL

GROUP BY manager_id;

添加复杂的筛选条件

SELECT COUNT(*),department_id

FROM employees

GROUP BY department_id;

SELECT job_id,MAX(salary),commission_pct

FROM employees

WHERE commission_pct IS NOT NULL

GROUP BY job_id

HAVING MAX(salary)>12000;

按表达式或函数分组

SELECT COUNT(),LENGTH(last_name) len_name

FROM employees

GROUP BY LENGTH(last_name)

HAVING COUNT()>5;

按多个字段分组

SELECT AVG(salary),job_id,department_id

FROM employees

GROUP BY job_id,department_id;

添加排序

SELECT AVG(salary),job_id,department_id

FROM employees

GROUP BY job_id,department_id

ORDER BY AVG(salary) DESC;