一句SQL完成动态分级查询
在最近的活字格项目中使用ActiveReports报表设计器设计一个报表模板时,遇到一个多级分类的难题:需要将某个部门所有销售及下属部门的销售金额汇总,因为下属级别的层次不确定,所以靠拼接子查询的方式显然是不能满足要求,经过一番实验,利用了CTE(Common Table Expression)很轻松解决了这个问题!
举例:有如下的部门表
以及员工表
如果想查询所有西北区的员工(包含西北、西安、兰州),如下图所示:
如何用CTE的方式实现呢?
Talk is cheap. Show me the code
-- 以下代码使用SQLite 3.18.0 测试通过WITH [depts]([dept_id]) AS( SELECT [d].[dept_id] FROM [dept] [d] JOIN [employees] [e] ON [d].[dept_id] = [e].[dept_id] WHERE [e].[emp_name] = '西北-经理' UNION ALL SELECT [d].[dept_id] FROM [dept] [d] JOIN [depts] [s] ON [d].[parent_id] = [s].[dept_id] )SELECT *FROM [employees]WHERE [dept_id] IN (SELECT [dept_id] FROM [depts]);
可能有些同学对CTE(Common Table Expression)还不太熟悉,这里简单说一下,有兴趣的同学可以google或者百度,介绍很多(这里以SQLite举例):
我还是更喜欢称CTE(Common Table Expression)为“公用表变量”而不是“公用表达式”,因为从行为和使用场景上讲,CTE更多的时候是产生(分迭代或者不迭代)结果集,供其后的语句使用(查询、插入、删除或更新),如上述的例子就是一个典型的利用迭代遍历树形结构数据。
CTE的优点:
递归的特点使得原本需要使用临时表、存储过程才能完成的逻辑,通过SQL就可以完成,尤其针对一些树或者是图的数据模型
因为是会话内的临时结果集,不需要去显示的声明或销毁
改写后的SQL语句可读性提高(看的明白才能修改)
给数据库引擎优化执行计划的可能性(这个不是肯定的,需要根据具体CTE的实现有关),优化了执行计划,自然地性能就能上升
为了更好的说明CTE的能力,这里附上两个例子(转自SQLite官网文档)
曼德勃罗集合(Mandelbrot set)
-- 以下代码使用SQLite 3.18.0 测试通过WITH RECURSIVE xaxis(x) AS (VALUES(-2.0) UNION ALL SELECT x+0.05 FROM xaxis WHERE x<1.2), yaxis(y) AS (VALUES(-1.0) UNION ALL SELECT y+0.1 FROM yaxis WHERE y<1.0), m(iter, cx, cy, x, y) AS ( SELECT 0, x, y, 0.0, 0.0 FROM xaxis, yaxis UNION ALL SELECT iter+1, cx, cy, x*x-y*y + cx, 2.0*x*y + cy FROM m WHERE (x*x + y*y) < 4.0 AND iter<28 ), m2(iter, cx, cy) AS ( SELECT max(iter), cx, cy FROM m GROUP BY cx, cy ), a(t) AS ( SELECT group_concat( substr(' .+*#', 1+min(iter/7,4), 1), '') FROM m2 GROUP BY cy )SELECT group_concat(rtrim(t),x'0a') FROM a;
运行后的结果,如下图:(使用SQLite Expert Personal 4.2 x64)
数独问题(Sudoku)
假设有类似下图的问题:
-- 以下代码使用SQLite 3.18.0 测试通过WITH RECURSIVE input(sud) AS ( VALUES('53..7....6..195....98....6.8...6...34..8.3..17...2...6.6....28....419..5....8..79') ), digits(z, lp) AS ( VALUES('1', 1) UNION ALL SELECT CAST(lp+1 AS TEXT), lp+1 FROM digits WHERE lp<9 ), x(s, ind) AS ( SELECT sud, instr(sud, '.') FROM input UNION ALL SELECT substr(s, 1, ind-1) || z || substr(s, ind+1), instr( substr(s, 1, ind-1) || z || substr(s, ind+1), '.' ) FROM x, digits AS z WHERE ind>0 AND NOT EXISTS ( SELECT 1 FROM digits AS lp WHERE z.z = substr(s, ((ind-1)/9)*9 + lp, 1) OR z.z = substr(s, ((ind-1)%9) + (lp-1)*9 + 1, 1) OR z.z = substr(s, (((ind-1)/3) % 3) * 3 + ((ind-1)/27) * 27 + lp + ((lp-1) / 3) * 6, 1) ) )SELECT s FROM x WHERE ind=0;
执行结果(结果中的数字就是对应格子中的答案)
附:SQLite中CTE(WITH关键字)语法图解:
WITH
cte-table-name
Select-stmt:
总结
CTE是解决一些特定问题的利器,但了解和正确的使用是前提,在决定将已有的一些SQL重构为CTE之前,确保对已有语句有清晰的理解以及对CTE足够的学习!Good Luck~~~
附件:用到的SQL脚本
![](/img/my/wx.png)
低调大师中文资讯倾力打造互联网数据资讯、行业资源、电子商务、移动互联网、网络营销平台。
持续更新报道IT业界、互联网、市场资讯、驱动更新,是最及时权威的产业资讯及硬件资讯报道平台。
转载内容版权归作者及来源网站所有,本站原创内容转载请注明来源。
- 上一篇
ELK日志分析单机系统详解
日志分析ELK平台,由ElasticSearch、Logstash和Kiabana三个开源工具组成。 官方网站:https://www.elastic.co/products Elasticsearch是个开源分布式搜索引擎,它的特点有:分布式,零配置,自动发现,索引自动分片,索引副本机制,restful风格接口,多数据源,自动搜索负载等。 Logstash是一个完全开源的工具,他可以对你的日志进行收集、过滤,并将其存储供以后使用(如,搜索)。 Kibana 也是一个开源和免费的工具,它Kibana可以为 Logstash 和 ElasticSearch 提供的日志分析友好的 Web 界面,可以帮助您汇总、分析和搜索重要数据日志。 ELK原理图: 如图:Logstash收集AppServer产生的Log,并存放到ElasticSearch集群中,而Kibana则从ES集群中查询数据生成图表,再返回给Browser。 ELK平台搭建 系统环境 System: Centos release 6.7(Final) ElasticSearch: elasticsearch-5.3.1.tar....
- 下一篇
Jenkins环境搭建总结
Jenkins是一个用Java编写的开源的持续集成(CI)工具,可持续、自动地构建/测试软件项目,监控一些定时执行的任务。具有开源,支持多平台和插件扩展,安装简单,界面化管理等特点。 一、Jenkins安装 1.安装OpenJDK [root@localhost~]#yuminstalljava-1.7.0-openjdk [root@localhost~]#java-version javaversion"1.7.0_131" OpenJDKRuntimeEnvironment(rhel-2.6.9.0.el6_8-x86_64u131-b00) OpenJDK64-BitServerVM(build24.131-b00,mixedmode) 2.yum安装Jenkins stable版本 [root@localhost~]#wget-O/etc/yum.repos.d/jenkins.repohttp://pkg.jenkins-ci.org/redhat-stable/jenkins.repo [root@localhost~]#rpm--importhttps://jenkin...
相关文章
文章评论
共有0条评论来说两句吧...