一句SQL完成动态分级查询

王伟廷发布于2019-08-28 17:46 / 3377人阅读

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

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

云数据库MySQL 动态sql 动态sql标签动态sql语句 mybatis动态sql

文章版权归作者所有，未经允许请勿转载,若此文章存在违规行为，您可以联系管理员删除。

转载请注明本文地址：https://www.ucloud.cn/yun/110784.html

SQL on Hadoop在快手大数据平台的实践与优化 | 分享实录

摘要：报表系统系统主要是处理以及常见的报表生成，额外的元数据系统是对外进行服务的。大表优化一个表有大量的子分区，它的过程会与元数据交互，获取所有的分区。 showImg(https://segmentfault.com/img/bVbtqW9?w=1080&h=720); 快手大数据架构工程师钟靓本文是根据快手大数据架构工程师钟靓于 5月18-19日在A2M人工智能与机器学习创新峰会《SQ...

hedge_hog 2019-07-01 16:14 评论0 收藏0
大数据时代数据库-云HBase架构&生态&实践

摘要：摘要第九届中国数据库技术大会，阿里云高级技术专家架构师封神曹龙带来题为大数据时代数据库云架构生态实践的演讲。主要内容有三个方面首先介绍了业务挑战带来的架构演进，其次分析了及生态，最后分享了大数据数据库的实际案例。数据备份及恢复。摘要： 2018第九届中国数据库技术大会，阿里云高级技术专家、架构师封神（曹龙）带来题为大数据时代数据库-云HBase架构&生态&实践的演讲。主要内容有三个方...

nanchen2251 2019-08-06 14:15 评论0 收藏0
大数据时代数据库-云HBase架构&生态&实践

摘要：摘要第九届中国数据库技术大会，阿里云高级技术专家架构师封神曹龙带来题为大数据时代数据库云架构生态实践的演讲。主要内容有三个方面首先介绍了业务挑战带来的架构演进，其次分析了及生态，最后分享了大数据数据库的实际案例。数据备份及恢复。摘要： 2018第九届中国数据库技术大会，阿里云高级技术专家、架构师封神（曹龙）带来题为大数据时代数据库-云HBase架构&生态&实践的演讲。主要内容有三个方...

econi 2019-06-25 18:39 评论0 收藏0
大数据时代数据库-云HBase架构&生态&实践

摘要：摘要第九届中国数据库技术大会，阿里云高级技术专家架构师封神曹龙带来题为大数据时代数据库云架构生态实践的演讲。主要内容有三个方面首先介绍了业务挑战带来的架构演进，其次分析了及生态，最后分享了大数据数据库的实际案例。数据备份及恢复。摘要： 2018第九届中国数据库技术大会，阿里云高级技术专家、架构师封神（曹龙）带来题为大数据时代数据库-云HBase架构&生态&实践的演讲。主要内容有三个方...

weknow619 2019-05-28 18:45 评论0 收藏0
磊哥测评之数据库SaaS篇：腾讯云控制台、DMC和小程序

摘要：是腾讯云提供的关系型数据库管理工具，目前支持和，腾讯云的数据库管理功能由控制台和小程序提供。小程序搜索腾讯云助手即可使用腾讯云助手集合了很多腾讯云产品，目前数据库产品支持和。本文由云+社区发表作者：腾讯云数据库随着云计算和数据库技术的发展，数据库正在变得越来越强大。数据库的性能如处理速度、对高并发的支持在节节攀升，同时分布式、实时的数据分析、兼容主流数据库等强大的性能正在越来越多...

william 2019-08-06 14:48 评论0 收藏0