Loading... ### MySQL:CTE 通用表达式 在MySQL中,CTE(Common Table Expressions,通用表表达式)是一种简洁而强大的语法,用于构建临时的结果集,通常用于简化复杂查询。CTE通过关键字 `WITH`引入,定义一个可以在后续查询中引用的临时结果集。CTE有两种类型:普通CTE和递归CTE。 #### 一、普通CTE 普通CTE用于定义一个非递归的临时结果集,通常用于提高查询的可读性和维护性。 ##### 1. 语法 普通CTE的基本语法如下: ```sql WITH cte_name (column1, column2, ...) AS ( SELECT column1, column2, ... FROM table_name WHERE condition ) SELECT * FROM cte_name; ``` ##### 2. 示例 假设我们有一个名为 `employees`的表,结构如下: ```sql CREATE TABLE employees ( emp_id INT, emp_name VARCHAR(100), dept_id INT, salary DECIMAL(10, 2) ); INSERT INTO employees (emp_id, emp_name, dept_id, salary) VALUES (1, 'Alice', 1, 5000.00), (2, 'Bob', 2, 6000.00), (3, 'Charlie', 1, 5500.00), (4, 'David', 3, 7000.00), (5, 'Eve', 2, 6500.00); ``` 我们希望查询部门ID为1的所有员工信息,可以使用普通CTE: ```sql WITH dept1_employees AS ( SELECT emp_id, emp_name, salary FROM employees WHERE dept_id = 1 ) SELECT * FROM dept1_employees; ``` 该查询结果为: ```plaintext +--------+----------+--------+ | emp_id | emp_name | salary | +--------+----------+--------+ | 1 | Alice | 5000.00| | 3 | Charlie | 5500.00| +--------+----------+--------+ ``` #### 二、递归CTE 递归CTE用于解决具有层级关系的数据查询问题,如组织结构、类别层级等。递归CTE由两个部分组成:锚定成员(非递归部分)和递归成员。 ##### 1. 语法 递归CTE的基本语法如下: ```sql WITH RECURSIVE cte_name (column1, column2, ...) AS ( -- 锚定成员 SELECT column1, column2, ... FROM table_name WHERE condition UNION ALL -- 递归成员 SELECT column1, column2, ... FROM cte_name JOIN table_name ON condition ) SELECT * FROM cte_name; ``` ##### 2. 示例 假设我们有一个名为 `employees`的表,包含员工及其经理的信息: ```sql CREATE TABLE employees ( emp_id INT, emp_name VARCHAR(100), manager_id INT ); INSERT INTO employees (emp_id, emp_name, manager_id) VALUES (1, 'Alice', NULL), (2, 'Bob', 1), (3, 'Charlie', 2), (4, 'David', 1), (5, 'Eve', 3); ``` 我们希望查询员工Alice的所有直接和间接下属,可以使用递归CTE: ```sql WITH RECURSIVE subordinates AS ( -- 锚定成员:Alice本身 SELECT emp_id, emp_name, manager_id FROM employees WHERE emp_name = 'Alice' UNION ALL -- 递归成员:Alice的下属及其下属 SELECT e.emp_id, e.emp_name, e.manager_id FROM employees e JOIN subordinates s ON e.manager_id = s.emp_id ) SELECT * FROM subordinates; ``` 该查询结果为: ```plaintext +--------+----------+------------+ | emp_id | emp_name | manager_id | +--------+----------+------------+ | 1 | Alice | NULL | | 2 | Bob | 1 | | 3 | Charlie | 2 | | 4 | David | 1 | | 5 | Eve | 3 | +--------+----------+------------+ ``` #### 三、CTE的优势 1. **简洁性**:CTE可以将复杂查询分解为多个易于理解的部分,提高代码的可读性。 2. **复用性**:定义一次CTE,可以在后续查询中多次引用,避免重复代码。 3. **递归查询**:递归CTE提供了一种优雅的方式来处理层级结构的数据。 #### 四、思维导图 ```plaintext CTE 通用表达式 │ ├── 普通CTE │ ├── 语法 │ └── 示例 │ └── 查询部门ID为1的员工信息 │ ├── 递归CTE │ ├── 语法 │ └── 示例 │ └── 查询员工Alice的所有下属 │ └── 优势 ├── 简洁性 ├── 复用性 └── 递归查询 ``` #### 五、总结 CTE(通用表表达式)为处理复杂查询提供了强大的工具。通过普通CTE,可以简化查询逻辑,提高可读性;通过递归CTE,可以优雅地处理层级结构数据。掌握CTE的使用,对于提升SQL查询能力和优化数据库操作有着重要意义。希望本文能帮助你更好地理解和使用MySQL中的CTE,提高工作效率和代码质量。 最后修改:2024 年 08 月 08 日 © 允许规范转载 打赏 赞赏作者 支付宝微信 赞 如果觉得我的文章对你有用,请随意赞赏