【新星计划】数据库 CTE 初识

最新推荐文章于 2024-05-21 18:34:19 发布
最新推荐文章于 2024-05-21 18:34:19 发布
阅读量880 收藏 1
点赞数 2
分类专栏： 数据库 文章标签： 数据库 mysql mssql sqlserver cte
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/superwfei/article/details/130424795
版权

【新星计划】数据库 CTE 初识

CTE

CTE指的是公共表表达式(Common Table Expression)

在日常我们使用数据库的时候，通常在一些数据汇总计算的时候，会用到临时表，或者变量或者其他辅助手段，来保存一些数据，然后与现有数据进行计算。

一个非常典型的例子，比如有一次考试，统计各个分数段的考生各有多少人，占考生比例为多少，平均分是多少，大于平均分的有多少人，占比多少。

在这个需求中，我们可以用以下几种方法

1、使用变量存储两个数据，平均值和总数
2、使用临时表存储这两个数据
3、使用子查询得出这两个数据

然后和汇总数据进行比较得出最后结果，但是，如果需求再复杂一点的时候，比如各个班的平均分是多少，总平均分是多少，那么使用变量方式就无法做到了。

而使用子查询方式，有一个很大的痛点，那就是，每个子查询只能使用一次，无法复用。

最后，临时表方式能解决这些问题，但是，也有不方便的地方，我们需要将查询分成多个部分，最后还要记得注销临时表

由于以上三种方式都有各自的痛点，所以在 SqlServer 2005 的时候，追加了另外一种方式，也就是 CTE 查询方式。而在 MySql ，则在 MySql v8 以上版本也得到了支持。

使用 CTE 有一些基本的优点：

1、查询结果可复用
2、可对查询结果生成多个 CTE 结果，互不干扰
3、数据存放在内存中，每次使用完毕后自动注销

CSDN 文盲老顾的博客， https://blog.csdn.net/supewrfei

CTE 语法

# MySql & MsSql
with cte as (
    select data1 colName1,data2 colName2
)
select * from cte;

with cte (colName1,colName2) as (
	select 1,2
)
select * from cte

基本 CTE 语法，在 MySql 和 MsSql 都一样，唯一需要注意的是，当出现多个查询时，MySql 因为每个指令都要求有分号，所以不会出现异常情况。而 MsSql 因为可以多个指令之间不用分号间隔，所以在多个指令中使用 cte 时，要在 with 前加一个分号，强制结束之前的指令，避免出现将 with 当做前一个指令查询后续部分。

老顾用 CTE 基本查询，更多的是用来模拟数据，减少了临时表的操作。

CTE 递归

在数据库中，使用了CTE后，发现优势比临时表多不到哪里去，一点也体现不出存放在内存中的优势，所以 CTE 做了一个骚操作：递归。

CTE 递归语法

# MySql 
with RECURSIVE CTE as (
    select data1 
    from sourceTable
    union all
    select * 
    from sourceTable,CTE
)
select * from CTE

# MsSql
    select data1 
    from sourceTable
    union all
    select * 
    from sourceTable,CTE
)
select * from CTE

在这里稍微有一点点不同，MySql 如果要使用CTE递归，必须对这个 CTE 做一个RECURSIVE 关键词修饰，而 MsSql 则无需多余的修饰。

递归示例

对于递归，我们先用一个最简单的例子来展示一下，生成100行数据，每行数据一个列，存放一个数字表示当前行顺序

with RECURSIVE t as (
	select 1 id
	union all
	select id + 1 
	from t
	where id < 100
)
select * from t;

指令通用的，MsSql 去掉 recursive 关键字修饰即可使用。通过这个例子，我们可以很方便的得到一个连续的数字区间。这对于数据补全丢失部分，有很大的帮助。

在 MsSql 中，递归默认不允许超出递归100次，如果一定要超出这个限制，需要通过 option 来指定递归限制数量，需要注意的是，该参数是在使用递归数据的查询位置，而不是在递归内

with t as (
	select 1 id
	union all
	select id+ 1 from t where id<200
) 
select * from t option(MAXRECURSION 0)

递归机制

那么，如果我们想知道，到底递归是怎么进行的，比如一次递归可以产生多少数据，每次递归都传递了哪些内容。

那么老顾做一个查询，来测试一下。

with RECURSIVE t as (
	select 1 id
	union all
	select id + 1 
	from t
	where id < 100
),t1 as (
	select *,1 tm 
	from t 
	where id < 5
	union all
	select t.*,tm + 1 
	from t,t1 
	where t.id=t1.id+1
)
select * from t1
order by tm,id;

在这里插入图片描述
我们对于 t1 这个 cte 递归表的第一部分，选择了四条数据，然后递归的时候，每次都会得到另外四条数据，我们通过 tm 就可以看到递归的层级。
在这里插入图片描述
然后，我们借助一下排名函数，来确定一下递归的更进一步的机制。

3575 - Recursive Common Table Expression ‘t1’ can contain neither aggregation nor window functions in recursive query block

但由于 MySql 不能在递归内使用排名函数，所以这次用MsSql 的指令看看结果。

with t as (
	select 1 id
	union all
	select id + 1 
	from t
	where id < 100
),t1 as (
	select *,1 tm,row_number() over(order by id) grp 
	from t 
	where id < 5
	union all
	select t.*,tm + 1,row_number() over(order by t.id) grp  
	from t,t1 
	where t.id=t1.id+1
)
select * from t1;

在这里插入图片描述
在这里插入图片描述

可以看到，除了递归入口的四条数据，其他数据的排名结果都是1，所以递归机制基本可以确定了。

1、递归方式为每次选出一条数据，作为递归数据传递
2、递归可以由一条数据产生多条数据，但不得产生与递归数据相同的数据，避免进入死循环
3、递归应该有跳出条件，或者是数据遍历完，或者是条件限制不满足
4、递归前后的列数保持一致，数据类型保持一致

然后就是不同的数据库，对 CTE 使用的限制了，这个需要各位自己慢慢体会了。比如刚才提到，MySql 中，CTE 递归不能使用排名函数。之前老顾的文章中提到，MsSql CTE 递归中，不能使用关联，不能使用行转列之类的。

几个CTE 递归的示例

由于 MsSql 和 MySql 的基础查询都差不多，我就只写一个了，大家自行调整适配自己的环境即可。

阶乘

嗯，就是一个简单示例，因为数据库中有数据类型，整型范围较小，所以生成不了多少数据

with t as (
	select 1 id,convert(bigint,1) as p
	union all
	select id + 1,p * (id + 1) 
	from t
	where id <= 15
)
select * from t order by id

在这里插入图片描述

斐波那契序列

with t as (
	select 1 num,convert(bigint,1) n1,convert(bigint,1) n2
	union all
	select num + 1,n2,n1 + n2 
	from t where num<50
) 
select * from t

在这里插入图片描述

无限级分类路径

with t as (
	select 1 id,0 parent,'顶级分类1' name
	union all select 2,0,'顶级分类2'
	union all select 3,1,'大项1'
	union all select 4,2,'大项2'
	union all select 5,1,'大项3'
	union all select 6,3,'小项1'
	union all select 7,3,'小项2'
	union all select 8,5,'小项3'
	union all select 9,5,'小项4'
	union all select 10,7,'细项1'
	union all select 11,7,'细项2'
),t1 as (
	select *,convert(varchar(max),id) pth,convert(nvarchar(max),name) npth 
	from t
	where parent=0
	union ALL
	select a.*,pth + '|' + convert(varchar,a.id),npth + ' => ' + a.name
	from t a,t1 b
	where a.parent=b.id
) 
select * from t1 order by pth

在这里插入图片描述
这个算是比较常用的了，扩展一下，加上每个项的排序也生成一个路径，那么基本就可以当做最后的内容使用了，直接做成视图。

rand 函数

不管是 mysql还是mssql，随机函数都是 rand，我们可以用 select rand() 得到一个随机的浮点数。

但是，在这两个数据库中， rand 的表现不尽相同，例如，我们在刚才的 cte 生成的 100行数据里，加上 rand() 方法，看看最后的区别。

# MySql
with RECURSIVE t as (
	select 1 id
	union all
	select id + 1 
	from t
	where id < 100
)
select *,rand() from t
order by id

在这里插入图片描述
MySql一切正常，每行都有一个随机数。

在看 MsSql 的表现。

#MsSql
with t as (
	select 1 id
	union all
	select id + 1 
	from t
	where id < 100
)
select *,rand() from t order by id

在这里插入图片描述
这是见了鬼了，好在不管是 MySql 还是 MsSql 都可以对 rand 函数指定一个种子，我们可以先生成一次固定的100行数据。

#MySql
with RECURSIVE t as (
	select 1 id
	union all
	select id + 1 
	from t
	where id < 100
)
select *,rand(id) from t
order by id

#MsSql
with t as (
	select 1 id
	union all
	select id + 1 
	from t
	where id < 100
)
select *,rand(id * 10000) from t order by id

MySql 结果
在这里插入图片描述
MsSql 结果
在这里插入图片描述
当然，MsSql 可以使用 checksum 和 newid 来辅助生成真正的随机数，毕竟 newid 是可以跨行的随机序列。

那么，在有了随机数的情况下，我们就可以使用 cte 递归来生成一些数据，而不用通过循环、游标之类的方式了。

小结

本文主要介绍了 CTE 的一些基本知识点，在无限级分类示例中，可以看到 cte 可以多次定义，方便我们对数据进一步处理，当查询需求比较复杂时，可以做多一些CTE内容，将数据都处理好了之后，最后再用一个总的查询将结果输出。以这种方式，对比子查询方式，执行计划更稳定。

在数据库中建立视图的时候，我们无法使用临时表之类的东西，而 CTE 却不受限制，所以用好 CTE ，对一些比较复杂的查询做成视图是有很大帮助的。

如果小伙伴们还有什么比较有趣的 CTE 使用示例或方法，也请告知老顾哦。
在这里插入图片描述

文盲老顾
关注
专栏目录
Greenplum 数据库架构分析及CTE实现_杨瑜@Pivotal.pdf
08-24
Greenplum数据库使用先进的查询优化器，可以根据数据分布和统计信息来选择最优的查询执行计划。优化器会考虑多种因素，如表连接顺序、并行度和数据访问策略等。 查询执行： 查询执行过程中，Greenplum使用了多种...
MySQL公共表表达式（Common Table Expressions, CTE）
Vincent的博客
11-23 3147
公共表表达式（Common Table Expressions, CTE）是MySQL在单一语句中的临时结果集。有时我们需要在一个SQL中重复执行同一个子查询，而每次子查询都会重新计算结果，带来性能的浪费。采用CTE可以在查询的一开始就定义好子查询的结果集，MySQL只会计算一次结果，然后在查询中可以反复引用。
【数据库】CTE（Common Table Expression(公共表表达式)）
在这里你大概率会有所收获，欢迎指错||纠正||建议||水评+点赞&&评论&&关注&&转发
07-25 1885
数据库CTE指的是公共表表达式(Common Table Expression). 可以把它认为是在单个select，insert,update, delete 或者 create view 语句中定义的临时结果集。 CTE类似派生表，但它不以对象的形式存储在数据库中，只在当前查询语句的执行期间有效。而且CTE可以在同一个语句中被多次引用。 CTE可以用于： 1. 建立递归查询。常见的场景有组织架构图、BOM等。具体用法请参考联机丛书。 2. 当不需要常规视图时，代替视图，提高可读性。 3. 允许在派生列上
公用表表达式（CTE）详解：针对 MySQL 和 SQL Server 数据库
nbsaas-boot基于Request-Response的企业级快速开发框架
05-21 3136
CTE 是一个命名的临时结果集，它在查询的执行范围内有效。CTE 的定义在WITH子句中，紧随其后的是查询主体。非递归 CTE：用于定义一次性计算结果的临时表。递归 CTE：用于处理递归查询，如处理层次结构数据（例如组织结构图）。
SQL——递归CTE
吴声子夜歌的博客
04-12 2212
简介 递归公用表达式（CTE）是一个CTE，它有一个子查询，它引用CTE名称本身。 语法： WITH RECURSIVE cte_name AS ( initial_query -- anchor member UNION ALL recursive_query -- 引用CTE名称的递归成员 SELECT * FROM cte_name; 递归CTE由三个主要部分组成： 初始查询，形成CTE结构的基本结果集。初始查询部分称为锚成员。 递归查询部分是引用CTE名称的查询，因此
SQL Server CTE 递归查询全解
a572893208的博客
02-04 1407
在TSQL脚本中，也能实现递归查询，SQL Server提供CTE（CommonTableExpression），只需要编写少量的代码，就能实现递归查询，本文详细介绍CTE递归调用的特性和使用示例，递归查询主要用于层次结构的查询，从叶级（Leaf Level）向顶层（Root Level）查询，或从顶层向叶级查询，或递归的路径（Path）。 一，递归查询原理 CTE的递归查询必...
银河麒麟V10 MySQL数据库安装包
11-11
4. **新的SQL功能**：引入了窗口函数、JSON操作增强、Common Table Expressions（CTE，公共表表达式）等新SQL特性，增强了查询能力和灵活性。 5. **改进的系统架构**：MySQL 8.0的存储过程、触发器、视图等对象现在...
数据库MySQL 8.0.37版本
最新发布
06-12
这可能包括窗口函数的扩展、CTE（公共表表达式）的优化以及JSON数据类型的增强。 在性能方面，MySQL 8.0.37可能会有更智能的查询优化器，能够更准确地估计查询执行成本，从而选择更优的执行计划。此外，可能还引入...
MySQL数据库面试题.pdf
05-31
48. **MySQL 8.0中的CTE和窗⼝函数**：CTE（公共表表达式）用于定义一个临时结果集，窗口函数则用于在一组相关的行上执行计算。 49. **JSON数据类型**：MySQL支持JSON数据类型的存储和操作。 50. **设置只读事务**...
mysql链接数据库驱动mysql8.0.17
05-17
MySQL是世界上最受欢迎的开源关系型数据库管理系统之一，其8.0.17版本引入了许多增强功能和优化，为开发者提供了更高效、安全的数据存储和管理解决方案。本文将深入探讨MySQL 8.0.17的链接数据库驱动以及相关知识点...
SQL 知识你知道吗：公共表表达式（CTE）
暗星涌动
06-12 987
公共表表达式（CTE）是一种临时命名查询结果集的方式，可以在一个查询语句中多次引用。CTE在查询语句中定义，可以在查询语句中的任何位置使用，并且只在查询语句执行期间存在。CTE通常用于简化复杂的查询，提高查询的可读性和可维护性。CTE的语法如下：其中，cte_name是CTE的名称，可以在查询语句的其他部分中使用。column1, column2等是CTE返回的列的名称。SELECT语句定义了CTE的查询结果集。最后的SELECT语句可以引用CTE并过滤、排序、分组等操作。
CTE的介绍与使用
haibucuoba的博客
06-11 5419
1、什么是CTE CTE可以被认为是在单个SELECT、INSERT、UPDATE、DELETE或CREATE VIEW语句的执行范围内定义的临时结果集。CTE类似于派生表，因为它不作为对象存储，并且仅在查询期间持续。与派生表不同，CTE可以自引用，并且可以在同一查询中多次使用。 2、CTE作用： 创建递归查询 在一般不是必要使用视图时替换视图，这样的话，...
公用表达式 Common Table Expressions - CTE
Eucalyptus's blog
12-19 1544
MySQL 从 8.0 开始支持 WITH 语法，即：Common Table Expressions - CTE，公用表表达式。公用表达式CTE（Common Table Expression，公共表表达式）是一个命名的临时结果集，它存在于单个语句的作用域中，以后可以在该语句中多次被引用。如下查询语句的即为公共表表达式，它是一个命名的（名字叫cte）结果集，而且在该条语句中它可以被当做临时表来引用（最后的select语句引用了它）。初次接触CTE是不是感觉跟派生表子查询很像？没错！
MySQL巧用公用表表达式（CTE）处理递归查询
MrXionGe的博客
11-28 1357
根据《MySQL 8.0 Reference Manual》的描述，Common Table Expressions（简称CTE）是一种名为临时结果集的表达式。它可以用来构造复杂的查询语句，并且可以在多个查询中重复使用同一个结果集。CTE的优点是可以使查询语句更加简洁、易于理解和维护，同时也可以提高查询性能。
CTE和递归查询
ssyniubi的博客
12-25 575
它类似于子查询，但可以在查询中多次引用，并且可以在同一查询中逐步构建。首先，基本查询选择根节点的数据。然后，递归查询通过连接基本查询的结果与组织表，以获取属于根节点下一级的子节点。CTE和递归查询可以结合使用，以实现对层次结构数据的递归查询。在这种情况下，CTE用于递归查询的基本查询，并且递归查询使用CTE的结果作为输入来生成递归的下一级查询。递归查询是一种特殊类型的查询，它可以在查询结果中包含自身的一部分。通过使用CTE和递归查询，可以轻松地查询组织结构的层次关系，包括所有子节点和子节点的子节点等等。
MySQL递归CTE（公用表表达式）
忧郁的蛋的专栏
10-04 638
公用表表达式（CTE）是一种为 MySQL 中每个查询生成的临时结果集命名的方法。WITH 子句用于定义 CTE，并且可以使用该子句在单个语句中定义多个 CTE。但是，CTE 只能引用先前在同一WITH 子句中定义的其他CTE。每个 CTE 的范围仅限于定义它的语句。递归 CTE 是一种使用自己的名称引用自身的子查询。要定义递归CTE，需要使用WITH RECURSIVE 子句，并且它必须有终止条件。递归 CTE 通常用于生成序列和遍历分层或树结构数据。
递归CTE
weixin_30745553的博客
07-06 241
递归CTE是Sql Server2005 中很重要的T-Sql 增强之一。SqlServer 最终还是通过纯基于集合的查询实现了递归查询。可以受益于递归查询的任务和操作包括操作图，树，层次结构等。这儿介绍一个递归CTE。 数据库：Northwind 表：Employees 要求：根据EmployeeID和ReportsTo属性维护的层次关系，返回输入员工和该员工所有级别的下属，包括属性：Emp...
MySQL5.7递归查询与CTE递归查询
llg___的博客
05-28 7389
MySQL 5.7递归查询，MySQL8.0的CET递归查询， This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA
递归 CTE
weixin_30622107的博客
07-16 625
公用表表达式 (CTE) 具有一个重要的优点，那就是能够引用其自身，从而创建递归 CTE。递归 CTE 是一个重复执行初始 CTE 以返回数据子集直到获取完整结果集的公用表表达式。 　　当某个查询引用递归 CTE 时，它即被称为递归查询。递归查询通常用于返回分层数据，例如：显示某个组织图中的雇员或物料清单方案（其中父级产品有一个或多个组件，而那些组件可能还有子组件，或者是其他父级产品的组...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

文盲老顾

你的鼓励将是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付：¥1
获取中
扫码支付

您的余额不足，请更换扫码支付或充值

打赏作者

实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值