MySQL(28)如何使用HAVING进行分组过滤？

HAVING 子句用于对 GROUP BY 之后的分组结果进行过滤。与 WHERE 子句不同，WHERE 子句在分组之前进行过滤，而 HAVING 子句则是在分组之后进行过滤。HAVING 子句通常与聚合函数一起使用，以便基于聚合函数的结果进行过滤。

基本语法

SELECT column1, AGGREGATE_FUNCTION(column2)
FROM table_name
GROUP BY column1
HAVING AGGREGATE_FUNCTION(column2) condition;

示例数据库和表结构

假设我们有一个公司数据库，其中有两个表：employees 和 departments。

-- 创建数据库
CREATE DATABASE company;

-- 选择数据库
USE company;

-- 创建表 departments
CREATE TABLE departments (
    dept_id INT AUTO_INCREMENT PRIMARY KEY,
    dept_name VARCHAR(100) NOT NULL
);

-- 创建表 employees
CREATE TABLE employees (
    emp_id INT AUTO_INCREMENT PRIMARY KEY,
    emp_name VARCHAR(100) NOT NULL,
    emp_position VARCHAR(100),
    emp_salary DECIMAL(10, 2),
    hire_date DATE,
    dept_id INT,
    FOREIGN KEY (dept_id) REFERENCES departments(dept_id)
);

-- 插入示例数据到 departments 表
INSERT INTO departments (dept_name)
VALUES 
('HR'),
('Engineering'),
('Marketing');

-- 插入示例数据到 employees 表
INSERT INTO employees (emp_name, emp_position, emp_salary, hire_date, dept_id)
VALUES 
('John Doe', 'Manager', 75000.00, '2023-10-01', 1),
('Jane Smith', 'Developer', 60000.00, '2023-09-01', 2),
('Alice Johnson', 'Analyst', 50000.00, '2023-08-01', 1),
('Bob Brown', 'Developer', 70000.00, '2023-07-01', 2),
('Charlie Brown', 'Tester', 55000.00, '2023-11-01', 3);

使用 HAVING 的示例

1. 按部门分组并计算每个部门的员工数量，仅显示员工数量大于1的部门

SELECT dept_id, COUNT(*) AS num_employees
FROM employees
GROUP BY dept_id
HAVING COUNT(*) > 1;

结果：

dept_id | num_employees
--------|---------------
1       | 2
2       | 2

2. 按职位分组并计算每个职位的平均薪水，仅显示平均薪水超过60000的职位

SELECT emp_position, AVG(emp_salary) AS avg_salary
FROM employees
GROUP BY emp_position
HAVING AVG(emp_salary) > 60000;

结果：

emp_position | avg_salary
-------------|------------
Developer    | 65000.00
Manager      | 75000.00

3. 按部门分组并计算每个部门的总薪水，仅显示总薪水超过120000的部门

SELECT dept_id, SUM(emp_salary) AS total_salary
FROM employees
GROUP BY dept_id
HAVING SUM(emp_salary) > 120000;

结果：

dept_id | total_salary
--------|--------------
1       | 125000.00
2       | 130000.00

使用 HAVING 与 JOIN 结合

HAVING 子句也可以与 JOIN 子句结合使用，以实现更复杂的过滤条件。

4. 按部门名称分组并计算每个部门的员工数量，仅显示员工数量大于1的部门

SELECT d.dept_name, COUNT(e.emp_id) AS num_employees
FROM employees e
JOIN departments d ON e.dept_id = d.dept_id
GROUP BY d.dept_name
HAVING COUNT(e.emp_id) > 1;

结果：

dept_name   | num_employees
------------|---------------
HR          | 2
Engineering | 2

5. 按部门名称分组并计算每个部门的总薪水，仅显示总薪水超过120000的部门

SELECT d.dept_name, SUM(e.emp_salary) AS total_salary
FROM employees e
JOIN departments d ON e.dept_id = d.dept_id
GROUP BY d.dept_name
HAVING SUM(e.emp_salary) > 120000;

结果：

dept_name   | total_salary
------------|--------------
HR          | 125000.00
Engineering | 130000.00

多条件 HAVING 子句

HAVING 子句可以包含多个条件，与 AND 和 OR 组合使用。

6. 按部门名称分组并计算每个部门的员工数量和总薪水，仅显示员工数量大于1且总薪水超过120000的部门

SELECT d.dept_name, COUNT(e.emp_id) AS num_employees, SUM(e.emp_salary) AS total_salary
FROM employees e
JOIN departments d ON e.dept_id = d.dept_id
GROUP BY d.dept_name
HAVING COUNT(e.emp_id) > 1 AND SUM(e.emp_salary) > 120000;

结果：

dept_name   | num_employees | total_salary
------------|---------------|--------------
HR          | 2             | 125000.00
Engineering | 2             | 130000.00

小结

通过使用 HAVING 子句，您可以对 GROUP BY 之后的分组结果进行过滤。HAVING 子句通常与聚合函数结合使用，以便基于聚合结果进行过滤。HAVING 子句也可以与 JOIN 子句结合使用，以实现更复杂的数据查询和分析。上述示例展示了不同情况下如何使用 HAVING 子句进行分组过滤，这在数据分析和报告中非常有用。