[TOC]
>[success] ## GROUP BY子句
> 可以使用GROUPBY子句将表中的数据分成若干组
:-: 
<br>
### **group by 语法**
:-: 
```sql
select分组函数，列(要求出现在group by的后面)
from表
[where筛选条件]
group by分组的列表
[order by子句]
```
**注意**：查询列表必须特殊，要求是分组函数和group by后出现的字段
<br>
<br>
### **group by的案例1：** 查询每个工种工资最高
```sql
SELECT
MAX( salary ),job_id
FROM
gin_employees
GROUP BY
x job_id;
```
<br>
### **group by的案例2：** 查询邮箱中包含a字符的，每个部门的平均工资
```sql
SELECT
AVG( salary ),
department_id
FROM
gin_employees
WHERE
email LIKE '%a%'
GROUP BY
Department_id
```
<br>
### **group by的案例3：** 查询有奖金的每个领导手下员工的最高工资
```sql
SELECT
MAX( salary ),
manager_id
FROM
gin_employees
WHERE
salary IS NOT NULL
GROUP BY
manager_id;
```
<br>
### **group by的案例4：** 查询那个部门员工个数大于2
```sql
SELECT
COUNT(*),
department_id
FROM
gin_employees
GROUP BY
Department_id
HAVING
COUNT(*) > 2
```
<br>
### **group by的案例5：** 查询每个工种有奖金的员工的最高工资>12000的工种编号和最高工资
```sql
SELECT
MAX( salary ),
job_id
FROM
gin_employees
WHERE
commission_pct IS NOT NULL
GROUP BY
job_id
HAVING
MAX( salary ) > 12000
```
<br>
### **group by的案例6：** 查询领导编号>102的每个领导手下的最低工资>5000的领导编号是哪个，以及其最低工资
```sql
SELECT
manager_id,
MIN( salary )
FROM
gin_employees
WHERE
manager_id > 102
GROUP BY
manager_id
HAVING
MIN(
salary) > 5000
```
<br>
1、分组查询中的筛选条件分为两类
                               数据源                                          位置                             关键字
分组前筛选             原始表                            group by子句的前面                 where
分组后筛选             分组后的结果集              group by子句的后面                 having
①：分组函数做条件肯定是放在havilng子句中
②：能用分组前筛选的，就优先考虑使用分组前筛选
<br>
<br>
### **group by的案例6：** 查询每个部门每个工种的员工平均工资
```sql
SELECT
AVG( salary ),
department_id,
job_id
FROM
gin_employees
GROUP BY
department_id,
job_id;
```
<br>
<br>
### **group by的案例6：** 查询每个部门每个工种的员工平均工资，并且按照平均工资降序排列，【由高到低，desc】
```sql
SELECT
AVG( salary ),
department_id,
job_id
FROM
gin_employees
GROUP BY
department_id,
job_id;
ORDER BY
AVG( salary ) DESC;-- 降序，由高到低
```
