MySQL with语句讲解

MySQL的WITH语句是一种基于查询执行前的预处理方式，可以以子查询为基础，使用别名并组合这些子查询，在一个查询中构建一个临时表，然后使用这个临时表进行后续操作。WITH语句可以避免数据冗余和混乱，提高查询效率，是优化MySQL查询相当重要的工具之一。下面是WITH语句的完整攻略。

1. 语句结构

WITH语句的语法结构如下：

WITH [RECURSIVE] cte_name (cte_column_list) AS (
    subquery1
    UNION [ALL|DISTINCT]
    subquery2
    UNION [ALL|DISTINCT]
    subqueryn
)
SELECT *
FROM cte_name;

RECURSIVE：表示是否为递归查询，可选项，不指定表示非递归查询。
cte_name：临时表的名称，表名必须唯一。
cte_column_list：临时表列的列表，包含列名和数据类型。
subquery1~subqueryn：子查询，可以包含连接、聚合、过滤等操作。
ALL|DISTINCT：指定UNION的操作是否去重，可选项，ALL表示不去重，DISTINCT表示去重。
SELECT：由WITH语句构建的临时表的查询操作。

2. 示例说明

示例1：使用WITH递归查询获取员工的上级领导

定义一个员工表emp，包含emp_id、emp_name和manager_id三列。现在要根据员工的emp_id查询其所属的上级领导，如果领导是员工自身，则返回NULL。

WITH RECURSIVE emp_cte AS (
    SELECT emp_id, emp_name, manager_id, CAST(NULL AS CHAR) AS leader_name
    FROM emp
    WHERE emp_id = 'emp001'
    UNION ALL
    SELECT emp.emp_id, emp.emp_name, emp.manager_id, emp_cte.emp_name
    FROM emp_cte, emp
    WHERE emp_cte.manager_id = emp.emp_id
)
SELECT *
FROM emp_cte;

解释：

首先在WITH语句中定义了一个递归的cte表emp_cte，查询了员工emp001的emp_id、emp_name和manager_id，同时给leader_name列设置为NULL；

接着使用UNION ALL操作，将查询结果合并为一个结果集。

在UNION ALL的右侧的查询，使用联结符inner join和emp_cte的manager_id比较，如果相等，则说明找到了该员工的上司。使用递归的方式，继续向上查找，直到找到最顶层的领导为止，同时查询结果中将其领导的姓名记录在leader_name列中。

最终使用SELECT查询语句打印出查询的结果，输出符合条件的员工及其领导姓名。

示例2：使用WITH查询所有员工及其直接上级与下级员工

定义一个员工表emp，包含emp_id、emp_name和manager_id三列。现在要查询所有员工的信息，包括其直接上级的信息和下级的信息。

WITH emp_cte AS (
    SELECT e1.emp_id, e1.emp_name, e1.manager_id, 'TOP' AS direction
    FROM emp e1
    LEFT JOIN emp e2 ON e1.manager_id = e2.emp_id
    WHERE e2.emp_id IS NULL
    UNION ALL
    SELECT e2.emp_id, e2.emp_name, e2.manager_id, 'DOWN' AS direction
    FROM emp_cte e1
    INNER JOIN emp e2 ON e1.emp_id = e2.manager_id
)
SELECT *
FROM emp_cte
ORDER BY manager_id, direction DESC;

解释：

首先定义一个公共表表达式emp_cte，在其中使用了UNION和UNION ALL两种情况，真正构建了一张包含公司内部关系的临时表。

使用LEFT JOIN和emp_cte的emp_id比较，如果emp_cte的emp_id为空则说明该员工是公司最高领导（没有上司），给其direction列设置为TOP。如果emp_cte的emp_id不为空，说明该员工有上司，给其direction列设置为DOWN。

所有的员工及其直接上司的信息都在表e1中，现在需要将这些员工的下属信息加入到查询结果中。使用INNER JOIN和emp_cte的emp_id比较，如果分别是上级和下级关系，则将下级员工信息记录在查询结果中，给其direction列设置为DOWN。

最终使用SELECT查询语句打印出查询的结果，输出符合条件的员工及其直接上级、下级员工的信息。