【SQL揭秘】有多少种数据库,就有多少类CTE
Common Table Expression
Common table expression简称CTE,由SQL:1999标准引入,可以认为是在单个 SELECT、INSERT、UPDATE、DELETE 或 CREATE VIEW 语句的执行范围内定义的临时结果集。CTE 与派生表类似,具体表现在不存储为对象,并且只在查询期间有效。与派生表的不同之处在于,CTE 可自引用,还可在同一查询中引用多次。
目前支持CTE的数据库有Teradata, DB2, Firebird, Microsoft SQL Server, Oracle (with recursion since 11g release 2), PostgreSQL (since 8.4), MariaDB (since 10.2), SQLite (since 3.8.3), HyperSQL and H2 (experimental), MySQL8.0.
CTE的语法如下:
1、Non-recursive CTEs
2、Recursive CTEs
CTE的使用
CTE使语句更加简洁
例如以下两个语句表达的是同一语义,使用CTE比未使用CTE的嵌套查询更简洁明了。
1) 使用嵌套子查询
2) 使用CTE
CTE 可以进行树形查询
初始化这颗树
1) 层序遍历
2) 深度优先遍历
Oracle
Oracle从9.2才开始支持CTE, 但只支持non-recursive with, 直到Oracle 11.2才完全支持CTE。但oracle 之前就支持connect by 的树形查询,recursive with 语句可以与connect by语句相互转化。 一些相互转化案例可以参考这里.
Oracle recursive with 语句不需要指定recursive关键字,可以自动识别是否recursive.Oracle 还支持CTE相关的hint,
“MATERIALIZE”告诉优化器产生一个全局的临时表保存结果,多次引用CTE时直接访问临时表即可。而”INLINE”则表示每次需要解析查询CTE。
PostgreSQL
PostgreSQL从8.4开始支持CTE,PostgreSQL还扩展了CTE的功能, CTE的query中支持DML语句,例如
MariaDB
MariaDB从10.2开始支持CTE。10.2.1 支持non-recursive CTE, 10.2.2开始支持recursive CTE。 目前的GA的版本是10.1.
MySQL
MySQL从8.0开始支持完整的CTE。MySQL8.0还在development
阶段,RC都没有,GA还需时日。
AliSQL
AliSQL基于mariadb10.2, port了no-recursive CTE的实现,此功能近期会上线。
以下从源码主要相关函数简要介绍其实现,
//解析识别with table引用
find_table_def_in_with_clauses
//检查依赖关系,比如不能重复定义with table名字
With_clause::check_dependencies
// 为每个引用clone一份定义
With_element::clone_parsed_spec
//替换with table指定的列名
With_element::rename_columns_of_derived_unit
此实现对于多次引用CTE,CTE会解析多次,因此此版本CTE有简化SQL的作用,但效率上没有效提高。
select count(*) from t1 where c2 !='z';
+----------+
| count(*) |
+----------+
| 65536 |
+----------+
1 row in set (0.25 sec)
//从执行时间来看是进行了3次全表扫描
with t as (select count(*) from t1 where c2 !='z')
select * from t union select * from t union select * from t;
+----------+
| count(*) |
+----------+
| 65536 |
+----------+
1 row in set (0.59 sec)
select count(*) from t1 where c2 !='z'
union
select count(*) from t1 where c2 !='z'
union
select count(*) from t1 where c2 !='z';
+----------+
| count(*) |
+----------+
| 65536 |
+----------+
1 row in set (0.57 sec)
![]()
explain select count(*) from t1 where c2 !='z'
union
select count(*) from t1 where c2 !='z'
union
select count(*) from t1 where c2 !='z';
以下是MySQL8.0 只扫描一次的执行计划
以下是PostgreSQL9.4 只扫描一次的执行计划
AliSQL还有待改进。
数据分析咨询请扫描二维码
若不方便扫码,搜微信号:CDAshujufenxi
在数字化浪潮中,数据驱动决策已成为企业发展的核心竞争力,数据分析人才的需求持续飙升。世界经济论坛发布的《未来就业报告》, ...
2025-03-28你有没有遇到过这样的情况?流量进来了,转化率却不高,辛辛苦苦拉来的用户,最后大部分都悄无声息地离开了,这时候漏斗分析就非 ...
2025-03-27TensorFlow Datasets(TFDS)是一个用于下载、管理和预处理机器学习数据集的库。它提供了易于使用的API,允许用户从现有集合中 ...
2025-03-26"不谋全局者,不足谋一域。"在数据驱动的商业时代,战略级数据分析能力已成为职场核心竞争力。《CDA二级教材:商业策略数据分析 ...
2025-03-26当你在某宝刷到【猜你喜欢】时,当抖音精准推来你的梦中情猫时,当美团外卖弹窗刚好是你想吃的火锅店…… 恭喜你,你正在被用户 ...
2025-03-26当面试官问起随机森林时,他到底在考察什么? ""请解释随机森林的原理""——这是数据分析岗位面试中的经典问题。但你可能不知道 ...
2025-03-25在数字化浪潮席卷的当下,数据俨然成为企业的命脉,贯穿于业务运作的各个环节。从线上到线下,从平台的交易数据,到门店的运营 ...
2025-03-25在互联网和移动应用领域,DAU(日活跃用户数)是一个耳熟能详的指标。无论是产品经理、运营,还是数据分析师,DAU都是衡量产品 ...
2025-03-24ABtest做的好,产品优化效果差不了!可见ABtest在评估优化策略的效果方面地位还是很高的,那么如何在业务中应用ABtest? 结合企业 ...
2025-03-21在企业数据分析中,指标体系是至关重要的工具。不仅帮助企业统一数据标准、提升数据质量,还能为业务决策提供有力支持。本文将围 ...
2025-03-20解锁数据分析师高薪密码,CDA 脱产就业班助你逆袭! 在数字化浪潮中,数据驱动决策已成为企业发展的核心竞争力,数据分析人才的 ...
2025-03-19在 MySQL 数据库中,查询一张表但是不包含某个字段可以通过以下两种方法实现:使用 SELECT 子句以明确指定想要的字段,或者使 ...
2025-03-17在当今数字化时代,数据成为企业发展的关键驱动力,而用户画像作为数据分析的重要成果,改变了企业理解用户、开展业务的方式。无 ...
2025-03-172025年是智能体(AI Agent)的元年,大模型和智能体的发展比较迅猛。感觉年初的deepseek刚火没多久,这几天Manus又成为媒体头条 ...
2025-03-14以下的文章内容来源于柯家媛老师的专栏,如果您想阅读专栏《小白必备的数据思维课》,点击下方链接 https://edu.cda.cn/goods/sh ...
2025-03-13以下的文章内容来源于刘静老师的专栏,如果您想阅读专栏《10大业务分析模型突破业务瓶颈》,点击下方链接 https://edu.cda.cn/go ...
2025-03-12以下的文章内容来源于柯家媛老师的专栏,如果您想阅读专栏《小白必备的数据思维课》,点击下方链接 https://edu.cda.cn/goods/sh ...
2025-03-11随着数字化转型的加速,企业积累了海量数据,如何从这些数据中挖掘有价值的信息,成为企业提升竞争力的关键。CDA认证考试体系应 ...
2025-03-10推荐学习书籍 《CDA一级教材》在线电子版正式上线CDA网校,为你提供系统、实用、前沿的学习资源,助你轻松迈入数据分析的大门! ...
2025-03-07在数据驱动决策的时代,掌握多样的数据分析方法,就如同拥有了开启宝藏的多把钥匙,能帮助我们从海量数据中挖掘出关键信息,本 ...
2025-03-06