SQL分组查询案例

r囧r小猫 2023-06-25 15:23 2阅读 0赞

## 总结 ##

### 语法 ###

`select` 查询列表  
`from` 表  
【`where` 筛选条件】  
`group by` 分组的字段  
`having` 分组后筛选  
【`order by` 排序的字段】;

### 执行顺序 ###

1.  from子句
2.  where子句
3.  group by子句
4.  having子句
5.  select子句
6.  order by子句

### 特点 ###

1.  和分组函数一同查询的字段必须是group by后出现的字段
2.  筛选分为两类：分组前筛选和分组后筛选

<table> 
 <thead> 
  <tr> 
   <th></th> 
   <th>针对的表</th> 
   <th>位置</th> 
   <th>连接的关键字</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>分组前筛选</td> 
   <td>原始表</td> 
   <td>group by前</td> 
   <td>where</td> 
  </tr> 
  <tr> 
   <td>分组后筛选</td> 
   <td>group by后的结果集</td> 
   <td>group by后</td> 
   <td>having</td> 
  </tr> 
 </tbody> 
</table>

## 表的结构 ##

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl8zNzU1MTAzNg_size_16_color_FFFFFF_t_70]  
数据文件参见：

[myemployees.sql文件][myemployees.sql]

## 1. 简单的分组 ##

### 案例1：查询每个工种的员工平均工资 ###

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

### 案例2：查询每个位置的部门个数 ###

SELECT COUNT(*),manager_id
    from employees
    WHERE manager_id is not null
    GROUP BY manager_id

## 2、可以实现分组前的筛选 ##

### 案例1：查询邮箱中包含a字符的 每个部门的最高工资 ###

SELECT MAX(salary) as 最高工资,department_id
    FROM employees
    where email like "%a%"
    GROUP BY department_id

### 案例2：查询有奖金的每个领导手下员工的平均工资 ###

SELECT AVG(salary) as 平均工资,manager_id
    from employees
    where commission_pct is not null
    GROUP BY manager_id

## 3、分组后筛选 ##

### 案例：查询哪个部门的员工个数>5 ###

#①查询每个部门的员工个数
    #② 筛选刚才①结果
    SELECT department_id,COUNT(*) as "员工个数"
    FROM employees
    GROUP BY department_id
    HAVING count(*)>5

注意是先找出每个部门的员工个数，再筛选出大于5的。所以是分组后筛选，要用HAVING（而不是WHERE），放在GROUP BY 的后面

### 案例2：每个工种有奖金的员工的最高工资>12000的工种编号和最高工资 ###

SELECT job_id, MAX(salary)
    from employees
    where commission_pct is not null
    GROUP BY job_id
    HAVING max(salary)>12000

### 案例3：领导编号>102的每个领导手下的最低工资大于5000的领导编号和最低工资 ###

select manager_id, min(salary)
    FROM employees
    WHERE manager_id>102
    GROUP BY manager_id
    having min(salary)>5000

## 4.添加排序 ##

### 案例：每个工种有奖金的员工的最高工资>6000的工种编号和最高工资,按最高工资升序 ###

SELECT job_id,MAX(salary)
    FROM employees
    WHERE commission_pct is not null
    GROUP BY job_id
    HAVING MAX(salary)>6000
    ORDER BY MAX(salary) ASC

## 5.按多个字段分组 ##

### 案例：查询每个工种每个部门的最低工资,并按最低工资降序 ###

SELECT job_id,department_id, MIN(salary)
    from employees
    GROUP BY job_id,department_id
    ORDER BY MIN(salary) DESC

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl8zNzU1MTAzNg_size_16_color_FFFFFF_t_70]: https://img-blog.csdnimg.cn/2019122918422412.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl8zNzU1MTAzNg==,size_16,color_FFFFFF,t_70
[myemployees.sql]: https://blog.csdn.net/GongmissYan/article/details/102937816