跳到主要内容

MySQL 通用表表达式

通用表表达式（Common Table Expressions，简称CTE）是MySQL中一种强大的工具，用于简化复杂查询并提高代码的可读性。CTE允许你定义一个临时的结果集，该结果集可以在查询中多次引用。对于初学者来说，CTE是处理复杂查询时的理想选择。

什么是通用表表达式？​

通用表表达式（CTE）是一个临时的命名结果集，它可以在一个SQL语句中多次引用。CTE通常用于简化复杂的查询，尤其是那些需要多次引用相同子查询的情况。CTE的语法简单易懂，适合初学者学习和使用。

基本语法​

CTE的基本语法如下：

sql
WITH cte_name AS (
    SELECT column1, column2, ...
    FROM table_name
    WHERE condition
)
SELECT * FROM cte_name;

在这个语法中，cte_name 是CTE的名称，括号内的查询定义了CTE的结果集。定义完CTE后，你可以在主查询中像使用普通表一样使用它。

使用CTE的示例​

让我们通过一个简单的示例来理解CTE的使用。

假设我们有一个名为 employees 的表，结构如下：

sql
CREATE TABLE employees (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    department VARCHAR(100),
    salary INT
);

表中包含以下数据：

sql
INSERT INTO employees (id, name, department, salary) VALUES
(1, 'Alice', 'HR', 50000),
(2, 'Bob', 'IT', 60000),
(3, 'Charlie', 'HR', 55000),
(4, 'David', 'IT', 65000),
(5, 'Eve', 'Finance', 70000);

现在，我们想要查询每个部门的平均工资，并找出工资高于部门平均工资的员工。我们可以使用CTE来实现这个需求：

sql
WITH department_avg_salary AS (
    SELECT department, AVG(salary) AS avg_salary
    FROM employees
    GROUP BY department
)
SELECT e.name, e.department, e.salary, d.avg_salary
FROM employees e
JOIN department_avg_salary d ON e.department = d.department
WHERE e.salary > d.avg_salary;

在这个查询中，我们首先定义了一个名为 department_avg_salary 的CTE，它计算了每个部门的平均工资。然后，我们在主查询中使用了这个CTE，找出工资高于部门平均工资的员工。

输出结果​

执行上述查询后，输出结果如下：

namedepartmentsalaryavg_salary
CharlieHR5500052500
DavidIT6500062500

递归CTE​

除了普通的CTE，MySQL还支持递归CTE。递归CTE允许你在查询中引用自身，通常用于处理层次结构数据，例如组织结构或树形结构。

递归CTE的语法​

递归CTE的基本语法如下：

sql
WITH RECURSIVE cte_name AS (
    -- 基础查询
    SELECT ...
    FROM ...
    WHERE ...

    UNION ALL

    -- 递归查询
    SELECT ...
    FROM cte_name
    WHERE ...
)
SELECT * FROM cte_name;

递归CTE的示例​

假设我们有一个表示组织结构的表 org_structure，结构如下：

sql
CREATE TABLE org_structure (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    manager_id INT
);

表中包含以下数据：

sql
INSERT INTO org_structure (id, name, manager_id) VALUES
(1, 'Alice', NULL),
(2, 'Bob', 1),
(3, 'Charlie', 2),
(4, 'David', 3),
(5, 'Eve', 1);

现在，我们想要查询某个员工的所有下属。我们可以使用递归CTE来实现这个需求：

sql
WITH RECURSIVE subordinates AS (
    -- 基础查询：找到直接下属
    SELECT id, name, manager_id
    FROM org_structure
    WHERE manager_id = 1

    UNION ALL

    -- 递归查询：找到下属的下属
    SELECT os.id, os.name, os.manager_id
    FROM org_structure os
    JOIN subordinates s ON os.manager_id = s.id
)
SELECT * FROM subordinates;

在这个查询中，我们首先找到直接下属（manager_id = 1），然后通过递归查询找到下属的下属，直到没有更多的下属为止。

输出结果​

执行上述查询后，输出结果如下：

idnamemanager_id
2Bob1
5Eve1
3Charlie2
4David3

实际应用场景​

CTE在实际应用中有很多用途，以下是一些常见的场景：

  1. 简化复杂查询：当查询中需要多次引用相同的子查询时，使用CTE可以避免重复代码，提高查询的可读性。
  2. 层次结构查询：递归CTE非常适合处理层次结构数据，例如组织结构、树形结构等。
  3. 分步查询：CTE允许你将复杂的查询分解为多个步骤，每个步骤都可以单独测试和调试。

总结​

通用表表达式（CTE）是MySQL中一个非常有用的工具，它可以帮助你简化复杂查询并提高代码的可读性。通过使用CTE，你可以将复杂的查询分解为多个简单的步骤，并且可以多次引用相同的子查询。递归CTE则特别适合处理层次结构数据。

希望本文能帮助你理解并掌握MySQL中的通用表表达式。如果你有任何问题或需要进一步的帮助，请参考以下资源：

练习​

  1. 使用CTE查询 employees 表中每个部门的最高工资。
  2. 使用递归CTE查询 org_structure 表中某个员工的所有上级。

通过完成这些练习，你将更好地理解CTE的使用方法。