公用表表达式（或通用表表达式）简称为CTE（Common Table Expressions）。CTE是一个命名的临时结果集，作用范围是当前语句。CTE可以理解成一个可以复用的子查询，当然跟子查询还是有点区别的，CTE可以引用其他CTE，但子查询不能引用其他子查询。所以，可以考虑代替子查询。

依据语法结构和执行方式的不同，公用表表达式分为普通公用表表达式和递归公用表表达式 2 种。

3.1 普通公用表表达式

普通公用表表达式的语法结构是：

  1. WITH CTE名称 
  2. AS （子查询）
  3. SELECT|DELETE|UPDATE 语句;

普通公用表表达式类似于子查询，不过，跟子查询不同的是，它可以被多次引用，而且可以被其他的普通公用表表达式所引用。

举例：查询员工所在的部门的详细信息。

  1. mysql> SELECT * FROM departments
  2.     -> WHERE department_id IN (
  3.     ->                  SELECT DISTINCT department_id
  4.     ->                  FROM employees
  5.     ->                  );
  6. +---------------+------------------+------------+-------------+
  7. | department_id | department_name  | manager_id | location_id |
  8. +---------------+------------------+------------+-------------+
  9. |            10 | Administration   |        200 |        1700 |
  10. |            20 | Marketing        |        201 |        1800 |
  11. |            30 | Purchasing       |        114 |        1700 |
  12. |            40 | Human Resources  |        203 |        2400 |
  13. |            50 | Shipping         |        121 |        1500 |
  14. |            60 | IT               |        103 |        1400 |
  15. |            70 | Public Relations |        204 |        2700 |
  16. |            80 | Sales            |        145 |        2500 |
  17. |            90 | Executive        |        100 |        1700 |
  18. |           100 | Finance          |        108 |        1700 |
  19. |           110 | Accounting       |        205 |        1700 |
  20. +---------------+------------------+------------+-------------+
  21. 11 rows in set (0.00 sec)

这个查询也可以用普通公用表表达式的方式完成：

  1. mysql> WITH emp_dept_id
  2.     -> AS (SELECT DISTINCT department_id FROM employees)
  3.     -> SELECT *
  4.     -> FROM departments d JOIN emp_dept_id e
  5.     -> ON d.department_id = e.department_id;
  6. +---------------+------------------+------------+-------------+---------------+
  7. | department_id | department_name  | manager_id | location_id | department_id |
  8. +---------------+------------------+------------+-------------+---------------+
  9. |            90 | Executive        |        100 |        1700 |            90 |
  10. |            60 | IT               |        103 |        1400 |            60 |
  11. |           100 | Finance          |        108 |        1700 |           100 |
  12. |            30 | Purchasing       |        114 |        1700 |            30 |
  13. |            50 | Shipping         |        121 |        1500 |            50 |
  14. |            80 | Sales            |        145 |        2500 |            80 |
  15. |            10 | Administration   |        200 |        1700 |            10 |
  16. |            20 | Marketing        |        201 |        1800 |            20 |
  17. |            40 | Human Resources  |        203 |        2400 |            40 |
  18. |            70 | Public Relations |        204 |        2700 |            70 |
  19. |           110 | Accounting       |        205 |        1700 |           110 |
  20. +---------------+------------------+------------+-------------+---------------+
  21. 11 rows in set (0.00 sec)

例子说明，公用表表达式可以起到子查询的作用。以后如果遇到需要使用子查询的场景，你可以在查询之前，先定义公用表表达式，然后在查询中用它来代替子查询。而且，跟子查询相比，公用表表达式有一个优点，就是定义过公用表表达式之后的查询，可以像一个表一样多次引用公用表表达式，而子查询则不能。

3.2 递归公用表表达式

递归公用表表达式也是一种公用表表达式，只不过，除了普通公用表表达式的特点以外，它还有自己的特点，就是可以调用自己。它的语法结构是：

  1. WITH RECURSIVE
  2. CTE名称 AS （子查询）
  3. SELECT|DELETE|UPDATE 语句;

递归公用表表达式由 2 部分组成，分别是种子查询和递归查询，中间通过关键字 UNION [ALL]进行连接。这里的种子查询，意思就是获得递归的初始值。这个查询只会运行一次，以创建初始数据集，之后递归查询会一直执行，直到没有任何新的查询数据产生，递归返回。

案例：针对于我们常用的employees表，包含employee_id，last_name和manager_id三个字段。如果a是b的管理者，那么，我们可以把b叫做a的下属，如果同时b又是c的管理者，那么c就是b的下属，是a的下下属。

下面我们尝试用查询语句列出所有具有下下属身份的人员信息。

如果用我们之前学过的知识来解决，会比较复杂，至少要进行 4 次查询才能搞定：

  • 第一步，先找出初代管理者，就是不以任何别人为管理者的人，把结果存入临时表；
  • 第二步，找出所有以初代管理者为管理者的人，得到一个下属集，把结果存入临时表；
  • 第三步，找出所有以下属为管理者的人，得到一个下下属集，把结果存入临时表。
  • 第四步，找出所有以下下属为管理者的人，得到一个结果集。

如果第四步的结果集为空，则计算结束，第三步的结果集就是我们需要的下下属集了，否则就必须继续进行第四步，一直到结果集为空为止。比如上面的这个数据表，就需要到第五步，才能得到空结果集。而且，最后还要进行第六步：把第三步和第四步的结果集合并，这样才能最终获得我们需要的结果集。

如果用递归公用表表达式，就非常简单了。我介绍下具体的思路。

  • 用递归公用表表达式中的种子查询，找出初代管理者。字段 n 表示代次，初始值为 1，表示是第一代管理者。
  • 用递归公用表表达式中的递归查询，查出以这个递归公用表表达式中的人为管理者的人，并且代次的值加 1。直到没有人以这个递归公用表表达式中的人为管理者了，递归返回。
  • 在最后的查询中，选出所有代次大于等于 3 的人，他们肯定是第三代及以上代次的下属了，也就是下下属了。这样就得到了我们需要的结果集。

这里看似也是 3 步，实际上是一个查询的 3 个部分，只需要执行一次就可以了。而且也不需要用临时表保存中间结果，比刚刚的方法简单多了。

代码实现：

  1. WITH RECURSIVE cte 
  2. AS 
  3. (
  4. SELECT employee_id,last_name,manager_id,1 AS n FROM employees WHERE employee_id = 100 -- 种子查询，找到第一代领导
  5. UNION ALL
  6. SELECT a.employee_id,a.last_name,a.manager_id,n+1 FROM employees AS a JOIN cte
  7. ON (a.manager_id = cte.employee_id) -- 递归查询，找出以递归公用表表达式的人为领导的人
  8. )
  9. SELECT employee_id,last_name FROM cte WHERE n >= 3;

总之，递归公用表表达式对于查询一个有共同的根节点的树形结构数据，非常有用。它可以不受层级的限制，轻松查出所有节点的数据。如果用其他的查询方式，就比较复杂了。

3.3 小 结

公用表表达式的作用是可以替代子查询，而且可以被多次引用。递归公用表表达式对查询有一个共同根节点的树形结构数据非常高效，可以轻松搞定其他查询方式难以处理的查询。