一句SQL完成动态分级查询

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

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

 

相关阅读:

【报表福利大放送】100余套报表模板免费下载

新手逆袭!记我的第一次系统开发

菜鸟是如何在三天内完成系统开发的?

 

相关文章
|
1天前
|
SQL 缓存 关系型数据库
SQL为什么不建议执行多表关联查询
本文探讨了SQL中不建议执行多表关联查询的原因,特别是MySQL与PG在多表关联上的区别。MySQL仅支持嵌套循环连接,而不支持排序-合并连接和散列连接,因此在多表(超过3张)关联查询时效率较低。文章还分析了多表关联查询与多次单表查询的效率对比,指出将关联操作放在Service层处理的优势,包括减少数据库计算资源消耗、提高缓存效率、降低锁竞争以及更易于分布式扩展等。最后,通过实例展示了如何分解关联查询以优化性能。
|
4天前
|
SQL 索引
【YashanDB知识库】字段加上索引后,SQL查询不到结果
【YashanDB知识库】字段加上索引后,SQL查询不到结果
|
4天前
|
SQL 大数据 数据挖掘
玩转大数据:从零开始掌握SQL查询基础
玩转大数据:从零开始掌握SQL查询基础
75 35
|
7天前
|
SQL 运维 监控
SQL查询太慢?实战讲解YashanDB SQL调优思路
本文是Meetup第十期“调优实战专场”的第二篇技术文章,上一篇《高效查询秘诀,解码YashanDB优化器分组查询优化手段》中,我们揭秘了YashanDB分组查询优化秘诀,本文将通过一个案例,助你快速上手YashanDB慢日志功能,精准定位“慢SQL”后进行优化。
|
15天前
|
SQL 关系型数据库 OLAP
云原生数据仓库AnalyticDB PostgreSQL同一个SQL可以实现向量索引、全文索引GIN、普通索引BTREE混合查询,简化业务实现逻辑、提升查询性能
本文档介绍了如何在AnalyticDB for PostgreSQL中创建表、向量索引及混合检索的实现步骤。主要内容包括:创建`articles`表并设置向量存储格式,创建ANN向量索引,为表增加`username`和`time`列,建立BTREE索引和GIN全文检索索引,并展示了查询结果。参考文档提供了详细的SQL语句和配置说明。
32 1
|
23天前
|
SQL 数据可视化 IDE
SQL做数据分析的困境,查询语言无法回答的真相
SQL 在简单数据分析任务中表现良好,但面对复杂需求时显得力不从心。例如,统计新用户第二天的留存率或连续活跃用户的计算,SQL 需要嵌套子查询和复杂关联,代码冗长难懂。Python 虽更灵活,但仍需变通思路,复杂度较高。相比之下,SPL(Structured Process Language)语法简洁、支持有序计算和分组子集保留,具备强大的交互性和调试功能,适合处理复杂的深度数据分析任务。SPL 已开源免费,是数据分析师的更好选择。
|
24天前
|
SQL 关系型数据库 分布式数据库
利用 PolarDB PG 版向量化引擎,加速复杂 SQL 查询!完成任务领发财新年抱枕!
利用 PolarDB PG 版向量化引擎,加速复杂 SQL 查询!完成任务领发财新年抱枕!
|
2月前
|
SQL Oracle 关系型数据库
如何在 Oracle 中配置和使用 SQL Profiles 来优化查询性能?
在 Oracle 数据库中,SQL Profiles 是优化查询性能的工具,通过提供额外统计信息帮助生成更有效的执行计划。配置和使用步骤包括:1. 启用自动 SQL 调优;2. 手动创建 SQL Profile,涉及收集、执行调优任务、查看报告及应用建议;3. 验证效果;4. 使用 `DBA_SQL_PROFILES` 视图管理 Profile。
|
2月前
|
SQL NoSQL Java
Java使用sql查询mongodb
通过MongoDB Atlas Data Lake或Apache Drill,可以在Java中使用SQL语法查询MongoDB数据。这两种方法都需要适当的配置和依赖库的支持。希望本文提供的示例和说明能够帮助开发者实现这一目标。
63 17
|
2月前
|
SQL Java 数据库连接
【潜意识Java】MyBatis中的动态SQL灵活、高效的数据库查询以及深度总结
本文详细介绍了MyBatis中的动态SQL功能,涵盖其背景、应用场景及实现方式。
161 6

热门文章

最新文章