MySQL CTE 通用表达式详解

目录

• 一、普通CTE
• 1. 语法
• 2. 示例
• 二、递归CTE
• 1. 语法
• 2. 示例

在mysql中，CTE（Common Table Expressions，通用表表达式）是一种简洁而强大的语法，用于构建临时的结果集，通常用于简化复杂查询。CTE通过关键字 WITH引入，定义一个可以在后续查询中引用的临时结果集。CTE有两种类型：普通CTE和递归CTE。

一、普通CTE

普通CTE用于定义一个非递归的临时结果集，通常用于提高查询的可读性和维护性。

1. 语法

普通CTE的基本语法如下：

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

2. 示例

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

CREATE TABLE employees (
    emp_id INT,
    emp_name VARCHAR(100),
    dept_id INT,
    salary DECIMALphp(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：

WITH dept1_employees 编程客栈AS (
    SELECT emp_id, emp_name, salary
    FROM employees
    WHERE dept_id = 1
)
SELECT *
FROM dept1_employees;
​

该查询结果为：

+--------+----------+--------+

| emp_id | emp_name | salary |

+--------+----------+--------+

| 1      | Alice    | 5000.00|

| 3      | Charlie 编程客栈 | 5500.00|

+--------+----------+--------+

二、递归CTE

递归CTE用于解决具有层级关系的数据查询问题，如组织结构、类别层级等。递归CTE由两个部分组成：锚定成员（非递归部分）和递归成员。

1. 语法

递归CTE的基本语法如下：

WITH RECURSIVE cte_name (column1, column2, ...)
AS
(
    -- 锚定成员
  ooIKtBvRe  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的表，包含员工及其经理的信息：

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：

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 subord编程客栈inates;
​

该查询结果为：

+--------+----------+------------+

| emp_id | emp_name | manager_id |

+--------+----------+------------+

| 1      | Alice    | NULL       |

| 2      | Bob      | 1          |

| 3      | Charlie  | 2          |

| 4      | David    | 1          |

| 5      | Eve      | 3          |

+--------+----------+------------+

到此这篇关于MySQL CTE 通用表达式的文章就介绍到这了,更多相关MySQL CTE 通用表达式内容请搜索编程客栈(www.devze.com)以前的文章或继续浏览下面的相关文章希望大家以后多多支持编程客栈(www.devze.com)！