文档章节

一句SQL完成动态分级查询

葡萄城技术团队
 葡萄城技术团队
发布于 2017/04/24 13:43
字数 999
阅读 77
收藏 2

在最近的活字格项目中使用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脚本

© 著作权归作者所有

葡萄城技术团队

葡萄城技术团队

粉丝 404
博文 583
码字总数 900180
作品 20
西安
高级程序员
私信 提问
自己手写一个Mybatis框架(简化)

继上一篇手写SpringMVC之后,我最近趁热打铁,研究了一下Mybatis。MyBatis框架的核心功能其实不难,无非就是动态代理和jdbc的操作,难的是写出来可扩展,高内聚,低耦合的规范的代码。本文完...

我叫刘半仙
2018/03/07
3K
3
项目上线,旧数据需要修改,写SQL太麻烦,看Excel配合简单SQL的强大功能

原文作者:弥诺R 原文地址:http://www.minuor.com/1523882302/article 转载声明:转载请注明原文地址,注意版权维护,谢谢! 实际场景 A项目前期上线后有两张表,第一张表里面有订单的基本信...

兴趣e族
2018/04/24
0
0
在使用ibatis实现多条件模糊查询的语句

大家一般能想到的做法是,将String语句进行拼串例如: String sql1="select * from 表名where"; String sql2="条件1"; String sql3="条件2"; String sql4="条件3"; ...... String sqln="条件n......

xinlll
2012/10/23
313
0
MySQL 慢查询slow log设置

MySQL的慢查询日志可以用来找出执行时间过长的查询语句,并进行针对性的优化。 一、slow log相关参数 以下参数都是动态参数,可以在实例运行时修改。 slowquerylog=1 #是否启用慢查询日志,1...

898009427
2018/03/29
0
0
eric_1989/jfire-sql

#Jfire-Sql框架 [TOC] ##框架说明## Jfire-sql是一个sql映射框架,通过将一个接口方法和sql语句绑定,来达到调用方法就是发出sql语句的目的。框架提供了透明自动的结果到对象,对象集合的转换...

eric_1989
2015/09/02
0
0

没有更多内容

加载失败,请刷新页面

加载更多

【2019个推开发者节】航母级APP都在用的SDK现在全部免费,35岁老程序员表示第一次见!

1024程序员节来了 双11近了 各路满减、折扣、领券、秒杀、集赞 营销玩法猛于虎,一看优惠两毛五 日常拼命赶“需求” 修“Bug”的开发者们 想找个好用又不贵的工具太难了 亲爱的开发者们,不要...

个推
32分钟前
9
0
Ceph对可用存储空间的校验与控制

Ceph一共使用了四个配置对可用存储空间进行校验并实施控制,如下: mon_osd_full_ratio:集群中的任一OSD空间使用率大于等于此数值时,集群将被标记为Full,此时集群将停止接受来自客户端的写...

浪里个浪浪
33分钟前
11
0
工厂方法模式

1.定义:创建一个接口,协助创建其它对象 2.优缺点 优: a.用户只需要知道这个工厂是创建哪种对象的,不需要知道创建的过程 b.满足开闭原则(开闭原则:对扩展开放,对修改关闭,即增加类可以...

wen123
35分钟前
6
0
Bootstrap Table -detailView和detailFilter的使用

查看表格 detailFilter 属性:data-detail-filter Type:Function Default:function(index,row){ return true} detailView 属性:data-detail-view Type:Boolean Default:false <table id="ta......

tianyawhl
40分钟前
4
0
场效应管的注意事项

  (1)为了安全使用场效应管,在线路的设计中不能超过管的耗散功率,最大漏源电压、最大栅源电压和最大电流等参数的极限值。   (2)各类型场效应管在使用时,都要严格按要求的偏置接入...

仙溪
43分钟前
6
0

没有更多内容

加载失败,请刷新页面

加载更多

返回顶部
顶部