在最近的项目中使用ActiveReports报表设计器设计一个报表模板时，遇到一个多级分类的难题：需要将某个部门所有销售及下属部门的销售金额汇总，因为下属级别的层次不确定，所以靠拼接子查询的方式显然是不能满足要求，经过一番实验，利用了CTE（Common Table Expression）很轻松解决了这个问题！
举例：有如下的部门表
以及员工表
如果想查询所有西北区的员工（包含西北、西安、兰州），如下图所示：
如何用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）
WITH RECURSIVE
xaxis(x) AS (VALUES(-2.0) ALL SELECT x+0.05 FROM xaxis WHERE x<1.2),yaxis(y) 1.0) SELECT y0.1 FROM yaxis WHERE y1.0),m(iter,cx,cy,x,y) AS (
0,y,0.0,0); line-height:1.5!important">0.0 FROM xaxis,yaxis
ALL
SELECT iter1,x*x-y*y + cx,0); line-height:1.5!important">2.0+ cy FROM m
WHERE (x*x + y*y) < 4.0 AND iter28
),m2(iter,cy) max(iter),cy FROM m GROUP BY cx,cy
),a(t) SELECT group_concat( substr( .+*#',0); line-height:1.5!important">1+min(iter/7,0); line-height:1.5!important">4),0); line-height:1.5!important">1),'')
FROM m2 BY cy
)
SELECT group_concat(rtrim(t),x0a') FROM a;
