MySQL 公用表表达式

MySQL 公用表表达式

一、简介

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

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

二、普通公用表表达式

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

with CTE名称
as （子查询）
select|update|delete 语句;

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

实例：

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

方式一：子查询

方式二：CTE 实现

三、递归公用表表达式

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

with recursive
cte名称 as （子查询）
select|update|delete 语句;

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

实例：

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

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

方式一：原来知识解决

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

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

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

方式二：递归公用表表达式

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

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

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

代码实现：

测试结果：

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

四、小结

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

关注林哥，持续更新哦！！！★,°:.☆(￣▽￣)/$:.°★ 。