发布于 

DQL -- 分组查询

本站字数：108k    本文字数：788    预计阅读时长：3min    访问次数：次

分组查询

概述

  • 语法

    1
    2
    3
    4
    5
    SELECT column, group_function(column)
    FROM table
    [WHERE condition]
    GROUP BY group_by_expression
    [ORDER BY column];

  • 注意

    查询列表必须特殊，要求时分组函数和group by后出现的字段

  • 特点

    1. 分组筛选中的筛选条件分为两类：

      分组状态 数据源 位置 关键字
      分组前筛选 原始表 group by子句的前面 where
      分组后筛选 分组厚的结果集 group by子句的后面 having
      • 分组函数最条件肯定是放在having子句中
      • 能用分组前少选的，就优先考虑使用分组前筛选

    2. group by语句支持单个字段分组，也可以支持多个 字段分组（多个字段之间用逗号隔开），表达式或函数（用的较少）

    3. 排序也可以被添加，是放在最后的

引入：查询每个部门的平均工资

1
2
3
4
SELECT DISTINCT
    department_id, AVG(salary)
FROM
    employees;

  • 案例1：查询每个工种的最高工资，并且降序排序

    1
    2
    3
    4
    5
    6
    SELECT 
        job_id, MAX(salary) MAX_SAL
    FROM
        employees
    GROUP BY job_id
    ORDER BY MAX_SAL DESC;

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

    1
    2
    3
    4
    5
    SELECT 
        COUNT(*), location_id
    FROM
        departments
    GROUP BY location_id;

添加筛选条件

  • 案例1：查询邮箱中包含a字符的，每个部门的平均工资

    1
    2
    3
    4
    5
    6
    7
    SELECT 
        AVG(salary), department_id
    FROM
        employees
    WHERE
        email LIKE '%a%'
    GROUP BY department_id;

  • 案例2：查询有奖金的每个领导手下员工的最高工资

    1
    2
    3
    4
    5
    6
    7
    SELECT 
        MAX(salary), manager_id
    FROM
        employees
    WHERE
        commission_pct IS NOT NULL
    GROUP BY manager_id;

添加复杂的筛选条件

  • 案例1：查询那个部门员工的个数大于2

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    ## 1. 查询每个部门的员工数
    SELECT 
        department_id, COUNT(*) DEP_COUNT
    FROM
        employees
    GROUP BY department_id;
    ## 2. 根据1的结果进行筛选(使用关键字 HAVING)
    SELECT 
        department_id, COUNT(*) DEP_COUNT
    FROM
        employees
    GROUP BY department_id
    HAVING DEP_COUNT > 2;

  • 案例2：查询每个工种有奖金的员工的最高工资大于12000的工种编号和最高工资 

    1
    2
    3
    4
    5
    6
    SELECT 
        job_id, MAX(salary) MAX_SAL
    FROM
        employees
    GROUP BY job_id
    HAVING MAX_SAL > 12000;

  • 案例3：查询领导编号大于120的每个领导手下的最低工资大于5000的林道编号是那个，及其最低工资

    1
    2
    3
    4
    5
    6
    7
    8
    SELECT 
        manager_id, MIN(salary) MIN_SAL
    FROM
        employees
    WHERE
        manager_id > 120
    GROUP BY manager_id
    HAVING MIN_SAL > 5000;

按表达式或函数分组

  • 案例：按员工行民的长度分组，查询每一组的员工个数，筛选员工个数大于5的有那些

    1
    2
    3
    4
    5
    6
    SELECT 
        LENGTH(last_name) NAME_LEN, COUNT(*)
    FROM
        employees
    GROUP BY NAME_LEN
    HAVING COUNT(*) > 5;

按多个字段分组

  • 案例：查询每个部门每个工种的员工的平均工资
1
2
3
4
5
SELECT 
    department_id, job_id, ROUND(AVG(salary), 2)
FROM
    employees
GROUP BY department_id , job_id;

相关测试

  1. 查询job_id的员工工资的最大值，最小值，平均值，总和，并按照job_id升序 

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    SELECT 
        job_id,
        MAX(salary),
        MIN(salary),
        ROUND(AVG(salary), 2),
        SUM(salary)
    FROM
        employees
    GROUP BY job_id
    ORDER BY job_id ASC;

  2. 查询员工最高工资和最低工资的差距（DIFFERENCE）

    1
    2
    3
    4
    SELECT 
        MAX(salary) - MIN(salary) DIFFERENCE
    FROM
        employees;

  3. 查询各个管理者手下员工的最低工资，其中最低工资不能低于6000,没有管理者的员工不计算在内 

    1
    2
    3
    4
    5
    6
    7
    8
    SELECT 
        manager_id, MIN(salary) MIN_SAL
    FROM
        employees
    WHERE
        manager_id IS NOT NULL
    GROUP BY manager_id
    HAVING MIN_SAL >= 6000;

  4. 查询所有部门的编号，员工数量和工资平均值，并按照平均工资降序

    1
    2
    3
    4
    5
    6
    SELECT 
        department_id, COUNT(*) STUFF_Q, AVG(salary) AVG_SAL
    FROM
        employees
    GROUP BY department_id
    ORDER BY AVG_SAL DESC;
许可协议

本文采用 署名-非商业性使用-相同方式共享 4.0 国际 许可协议，转载请注明出处。