【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还有待改进。

原文发布于微信公众号 - 数据和云(OraNews)

原文发表时间:2017-07-06

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏好好学java的技术栈

bat等大公司常考java多线程面试题

简而言之,进程是程序运行和资源分配的基本单位,一个程序至少有一个进程,一个进程至少有一个线程.进程在执行过程中拥有独立的内存单元,而多个线程共享内存资源,减少切...

684
来自专栏Golang语言社区

《GO IN ACTION》读后记录:GO的并发与并行

一、使用goroutine来运行程序 1. Go的并发与并行 Go的并发能力,是指让某个函数独立于其他函数运行的能力。当为一个函数创建goroutine时,该函...

3907
来自专栏函数式编程语言及工具

FunDA(13)- 示范:用户自定义操作函数 - user defined tasks

   FunDA是一种函数式的编程工具,它所产生的程序是由许多功能单一的细小函数组合而成,这些函数就是用户自定义操作函数了。我们在前面曾经提过FunDA的运作原...

1748
来自专栏纯洁的微笑

当CPU飙高时,它在做什么

822
来自专栏Pythonista

Golang之http编程

803
来自专栏张善友的专栏

Quartz.net官方开发指南 第一课:使用Quartz.net

使用scheduler之前应首先实例化它。使用SchedulerFactory可以完成scheduler的实例化。用户可直接地实例化这个工厂类并且直接使用工厂的...

20010
来自专栏猿人谷

进程和线程的区别

进程和线程的区别 简而言之,一个程序至少有一个进程,一个进程至少有一个线程. 线程的划分尺度小于进程,使得多线程程序的并发性高。 另外,进程在执行过程中拥有独...

1885
来自专栏跟着阿笨一起玩NET

关于使用CTE(公用表表达式)的递归查询

本文转载:http://www.cnblogs.com/shuangnet/archive/2013/03/22/2975929.html

592
来自专栏逸鹏说道

C# 温故而知新: 线程篇(三)下

结果计时器会一直滚动,因为a对象被锁住,除非完成Thread.Sleep(3000000)后才能进入到a共享区 ? 由于以上的问题,微软还是建议我们使用一个私有...

3596
来自专栏程序员的SOD蜜

使用CTE解决复杂查询的问题

最近,同事需要从数个表中查询用户的业务和报告数据,写了一个SQL语句,查询比较慢: Select S.Name, S.AccountantCode, ( Se...

2096

扫码关注云+社区