[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;-- 降序，由高到低
```
