请选择 进入手机版 | 继续访问电脑版
 找回密码
 立即注册

QQ登录

只需一步,快速开始

断天涯大虾
社区贡献组   /  发表于:2017-4-24 14:00  /   查看:4880  /  回复:0
本帖最后由 断天涯大虾 于 2017-4-24 14:02 编辑

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

举例:有如下的部门表
以及员工表
如果想查询所有西北区的员工(包含西北、西安、兰州),如下图所示:


如何用CTE的方式实现呢?
Talk is cheap. Show me the code
  1. -- 以下代码使用SQLite 3.18.0 测试通过
  2. WITH
  3.     [depts]([dept_id]) AS(
  4.         SELECT [d].[dept_id]
  5.         FROM   [dept] [d]
  6.                JOIN [employees] [e] ON [d].[dept_id] = [e].[dept_id]
  7.         WHERE  [e].[emp_name] = '西北-经理'
  8.         UNION ALL
  9.         SELECT [d].[dept_id]
  10.         FROM   [dept] [d]
  11.                JOIN [depts] [s] ON [d].[parent_id] = [s].[dept_id]
  12.     )
  13. SELECT *
  14. FROM   [employees]
  15. WHERE  [dept_id] IN (SELECT [dept_id]
  16.        FROM   [depts]);
复制代码
可能有些同学对CTE(Common Table Expression)还不太熟悉,这里简单说一下,有兴趣的同学可以google或者百度,介绍很多(这里以SQLite举例):
我还是更喜欢称CTE(Common Table Expression)为“公用表变量”而不是“公用表达式”,因为从行为和使用场景上讲,CTE更多的时候是产生(分迭代或者不迭代)结果集,供其后的语句使用(查询、插入、删除或更新),如上述的例子就是一个典型的利用迭代遍历树形结构数据。
CTE的优点:
  • 递归的特点使得原本需要使用临时表、存储过程才能完成的逻辑,通过SQL就可以完成,尤其针对一些树或者是图的数据模型
  • 因为是会话内的临时结果集,不需要去显示的声明或销毁
  • 改写后的SQL语句可读性提高(看的明白才能修改)
  • 给数据库引擎优化执行计划的可能性(这个不是肯定的,需要根据具体CTE的实现有关),优化了执行计划,自然地性能就能上升


为了更好的说明CTE的能力,这里附上两个例子(转自SQLite官网文档)
曼德勃罗集合(Mandelbrot set)
  1. -- 以下代码使用SQLite 3.18.0 测试通过
  2. WITH RECURSIVE
  3.   xaxis(x) AS (VALUES(-2.0) UNION ALL SELECT x+0.05 FROM xaxis WHERE x<1.2),
  4.   yaxis(y) AS (VALUES(-1.0) UNION ALL SELECT y+0.1 FROM yaxis WHERE y<1.0),
  5.   m(iter, cx, cy, x, y) AS (
  6.     SELECT 0, x, y, 0.0, 0.0 FROM xaxis, yaxis
  7.     UNION ALL
  8.     SELECT iter+1, cx, cy, x*x-y*y + cx, 2.0*x*y + cy FROM m
  9.      WHERE (x*x + y*y) < 4.0 AND iter<28
  10.   ),
  11.   m2(iter, cx, cy) AS (
  12.     SELECT max(iter), cx, cy FROM m GROUP BY cx, cy
  13.   ),
  14.   a(t) AS (
  15.     SELECT group_concat( substr(' .+*#', 1+min(iter/7,4), 1), '')
  16.     FROM m2 GROUP BY cy
  17.   )
  18. SELECT group_concat(rtrim(t),x'0a') FROM a;
复制代码
运行后的结果,如下图:(使用SQLite Expert Personal 4.2 x64)
数独问题(Sudoku)
假设有类似下图的问题:
  1. -- 以下代码使用SQLite 3.18.0 测试通过
  2. WITH RECURSIVE
  3.   input(sud) AS (
  4.     VALUES('53..7....6..195....98....6.8...6...34..8.3..17...2...6.6....28....419..5....8..79')
  5.   ),
  6.   digits(z, lp) AS (
  7.     VALUES('1', 1)
  8.     UNION ALL SELECT
  9.     CAST(lp+1 AS TEXT), lp+1 FROM digits WHERE lp<9
  10.   ),
  11.   x(s, ind) AS (
  12.     SELECT sud, instr(sud, '.') FROM input
  13.     UNION ALL
  14.     SELECT
  15.       substr(s, 1, ind-1) || z || substr(s, ind+1),
  16.       instr( substr(s, 1, ind-1) || z || substr(s, ind+1), '.' )
  17.      FROM x, digits AS z
  18.     WHERE ind>0
  19.       AND NOT EXISTS (
  20.             SELECT 1
  21.               FROM digits AS lp
  22.              WHERE z.z = substr(s, ((ind-1)/9)*9 + lp, 1)
  23.                 OR z.z = substr(s, ((ind-1)%9) + (lp-1)*9 + 1, 1)
  24.                 OR z.z = substr(s, (((ind-1)/3) % 3) * 3
  25.                         + ((ind-1)/27) * 27 + lp
  26.                         + ((lp-1) / 3) * 6, 1)
  27.          )
  28.   )
  29. SELECT s FROM x WHERE ind=0;
复制代码
执行结果(结果中的数字就是对应格子中的答案)

附:SQLite中CTE(WITH关键字)语法图解:
WITH

cte-table-name

Select-stmt:


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

   
关于葡萄城:全球最大的控件提供商,世界领先的企业应用定制工具、企业报表和商业智能解决方案提供商,为超过75%的全球财富500强企业提供服务。

0 个回复

您需要登录后才可以回帖 登录 | 立即注册
返回顶部