SQLite中使用CTE巧解多级分类的级联查询
在最近的活字格项目中使用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的优点:
为了更好的说明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 ( SELECT 0,y,0.0,0); ">0.0 FROM xaxis,yaxis ALL SELECT iter1,x*x-y*y + cx,0); ">2.0+ cy FROM m WHERE (x*x + y*y) < 4.0 AND iter28 ),m2(iter,cy) SELECT max(iter),cy FROM m GROUP BY cx,cy ),a(t) SELECT group_concat( substr( .+*#',0); ">1+min(iter/7,0); ">4),0); ">1),'') FROM m2 BY cy ) SELECT group_concat(rtrim(t),x0a') FROM a;运行后的结果,如下图:(使用SQLite Expert Personal 4.2 x64)
数独问题(Sudoku) 假设有类似下图的问题: WITH RECURSIVE input(sud) VALUES(53..7....6..195....98....6.8...6...34..8.3..17...2...6.6....28....419..5....8..79') ),digits(z,lp) 11) SELECT CAST(lp1 AS TEXT),lpFROM digits WHERE lp9 ),x(s,ind) SELECT sud,instr(sud,0);">.FROM input SELECT substr(s,1) || z || substr(s,0); ">1),instr( substr(s,0);">' ) FROM x,digits AS z WHERE ind>0 AND NOT EXISTS ( 1 AS lp WHERE z.z = substr(s,((ind1)9)*9 + lp,0); ">1) OR z.z %9) + (lp+ 3) % * 3 + ((ind27) 27 + lp + ((lp/ 6,0); ">1) ) ) SELECT s FROM x =0; 执行结果(结果中的数字就是对应格子中的答案) 附:SQLite中CTE(WITH关键字)语法图解: WITH cte-table-name Select-stmt: 总结CTE是解决一些特定问题的利器,但了解和正确的使用是前提,在决定将已有的一些SQL重构为CTE之前,确保对已有语句有清晰的理解以及对CTE足够的学习!Good Luck~~~ 附件:用到的SQL脚本 (编辑:李大同) 【声明】本站内容均来自网络,其相关言论仅代表作者个人观点,不代表本站立场。若无意侵犯到您的权利,请及时与联系站长删除相关内容! |