在最近的活字格项目中使用ActiveReports报表设计器设计一个报表模板时,遇到一个多级分类的难题：需要将某个部门所有销售及下属部门的销售金额汇总,因为下属级别的层次不确定,所以靠拼接子查询的方式显然是不能满足要求,经过一番实验,利用了CTE(Common Table Expression)很轻松办理了这个问题!

举例：有如下的部分表

以及员工表

如果想查询所有西北区的员工(包括西北、西安、兰州),如下图所示：

如何用CTE的方式实现呢?

Talk is cheap. Show me the code

可能有些同学对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~~~

关于葡萄城

葡萄城是一家跨国软件研发集团,专注控件领域近30年,是全球最年夜的控件提供商,也是微软认证的金牌合作伙伴