举例：有如下的部门表

以及员工表

如果想查询所有西北区的员工（包含西北、西安、兰州），如下图所示：

如何用CTE的方式实现呢？

Talk is cheap. Show me the code

-- 以下代码使用sqlite 3.18.0 测试通过
WITH
    [depts](dept_id]) AS(
        SELECT d].]
        FROM   dept] ]
               JOIN employeese] ON ] = WHERE  emp_name'西北-经理'
        UNION ALL
        sparent_id]
    )
SELECT *
]
IN (]
       ]);

可能有些同学对CTE（Common Table Expression）还不太熟悉，这里简单说一下，有兴趣的同学可以google或者百度，介绍很多（这里以sqlite举例）：

我还是更喜欢称CTE（Common Table Expression）为“公用表变量”而不是“公用表达式”，因为从行为和使用场景上讲，CTE更多的时候是产生（分迭代或者不迭代）结果集，供其后的语句使用（查询、插入、删除或更新），如上述的例子就是一个典型的利用迭代遍历树形结构数据。

CTE的优点：

• 递归的特点使得原本需要使用临时表、存储过程才能完成的逻辑，通过sql就可以完成，尤其针对一些树或者是图的数据模型
• 因为是会话内的临时结果集，不需要去显示的声明或销毁
• 改写后的sql语句可读性提高（看的明白才能修改）
• 给数据库引擎优化执行计划的可能性（这个不是肯定的，需要根据具体CTE的实现有关），优化了执行计划，自然地性能就能上升

为了更好的说明CTE的能力，这里附上两个例子（转自sqlite官网文档）

曼德勃罗集合（Mandelbrot set）

运行后的结果，如下图：（使用sqlite Expert Personal 4.2 x64）

数独问题（Sudoku）

假设有类似下图的问题：

执行结果（结果中的数字就是对应格子中的答案）

附：sqlite中CTE（WITH关键字）语法图解：

WITH

cte-table-name

Select-stmt:

总结

CTE是解决一些特定问题的利器，但了解和正确的使用是前提，在决定将已有的一些sql重构为CTE之前，确保对已有语句有清晰的理解以及对CTE足够的学习！Good Luck~~~

附件：用到的SQL脚本