- 浏览: 1075589 次
- 性别:
- 来自: 武汉
文章分类
最新评论
-
flyfeifei66:
list<bean> bean 中有 list&l ...
freemarker中的list -
BelloVersion:
第五种错误Remote host closed connect ...
客户端如何使用httpclient向https服务器发送数据 -
willxue:
看了半天 前面说的是错的?。。。
反向键索引的原理和用途 -
liulanghan110:
quainter 写道麻烦博主,参数为数组时,paramete ...
MYBATIS 的parameter -
quainter:
麻烦博主,参数为数组时,parameterType怎么写啊?
MYBATIS 的parameter
公用表表达式
(CTE)
具有一个重要的优点,那就是能够引用其自身,从而创建递归
CTE
。递归
CTE
是一个重复执行初始
CTE
以返回数据子集直到获取完整结果集的公用表表达式。
递归 CTE 可以极大地简化在 SELECT 、 INSERT 、 UPDATE 、 DELETE 或 CREATE VIEW 语句中运行递归查询所需的代码。
递归 CTE 的结构
递归 CTE 由下列三个元素组成:
1.
例程的调用。
递归
CTE
的第一个调用包括一个或多个由
UNION ALL
、
UNION
、
EXCEPT
或
INTERSECT
运算符联接的
CTE_query_definitions
。由于这些查询定义形成了
CTE
结构的基准结果集,所以它们被称为
“
定位点成员
”
。
CTE_query_definitions
被视为定位点成员,除非它们引用了
CTE
本身。所有定位点成员查询定义必须放置在第一个递归成员定义之前,而且必须使用
UNION ALL
运算符联接最后一个定位点成员和第一个递归成员。
2.
例程的递归调用。
递归调用包括一个或多个由引用
CTE
本身的
UNION ALL
运算符联接的
CTE_query_definitions
。这些查询定义被称为
“
递归成员
”
。
3.
终止检查。
终止检查是隐式的;当上一个调用中未返回行时,递归将停止。
注意: |
如果递归 CTE 组合不正确,可能会导致无限循环。例如,如果递归成员查询定义对父列和子列返回相同的值,则会造成无限循环。在测试递归查询的结果时,可以通过在 INSERT 、 UPDATE 、 DELETE 或 SELECT 语句的 OPTION 子句中使用 MAXRECURSION 提示和 0 到 32,767 之间的值,来限制特定语句允许的递归级数。 |
伪代码和语义
递归 CTE 结构必须至少包含一个定位点成员和一个递归成员。以下伪代码显示了包含一个定位点成员和一个递归成员的简单递归 CTE 的组件。
WITH
cte_name
(
column_name
[
,...
n
]
)
AS
(
CTE_query_definition
-- Anchor member is defined.
UNION ALL
CTE_query_definition
-- Recursive member is defined referencing cte_name.
)
-- Statement using the CTE
SELECT
*
FROM
cte_name
递归执行的语义如下:
1. 将 CTE 表达式拆分为定位点成员和递归成员。
2. 运行定位点成员,创建第一个调用或基准结果集 (T 0 ) 。
3. 运行递归成员,将 T i 作为输入,将 T i+1 作为输出。
4. 重复步骤 3 ,直到返回空集。
5. 返回结果集。这是对 T 0 到 T n 执行 UNION ALL 的结果。
下面举两个例子,
例子 1 :
CREATE TABLE Test ( Name VARCHAR ( 50 ), Parentname VARCHAR ( 50 ));
INSERT INTO Test values (' 仙桃 ',' 湖北 '),(' 湖北 ',' 中国 ')
INSERT INTO Test (name) values (' 中国 ')
需求:查询一个名称的所有,上级名称。如仙桃,则是 中国湖北仙桃,湖北,则是 中国湖北。
with
city
(
str
,
name
,
Parentname
)
as
(
select name
,
name
,
Parentname
from
test
where name
=
'
仙桃
'
UNION ALL
select
a
.
str
||
b
.
name
,
b
.
name
,
b
.
Parentname
from
city
a
,
test
b
where
a
.
Parentname
=
b
.
name
)
select
*
from
city
可以看到,结果集是递归程序每一步的结果的并集。我们需要的结果是第三个。可以改一下select语句
select * from city where partentname is null
例子 2 :
CREATE TABLE Test ( User_Name VARCHAR ( 12 ), City VARCHAR ( 12 ));
INSERT INTO
Test
(
User_Name
,
City
)
values ('
张三
','
杭州
'),('
张三
','
郑州
'),('
李四
','
杭州
'),
(' 张三 ',' 南昌 '),(' 李四 ',' 广州 '),(' 王五 ',' 北京 ');
表如下:
需求:查找每个人到过的所有城市。
这相当于一个字符串分组连接的例子。可以利用双重 FOR 循环来解决。第一个 FOR ,循环名字,第二个 FOR ,根据名字来循环城市。也可以利用 with 语句来实现。
这里使用两个 with ,第一个的记录如下:
我们只要再构建一个结果集,控制 RK_NUM 的值为 1 , 2 , 3 ,然后两个表关联查询就可以得到结果。
第二个初始时有三个记录,如下 :
第二次 ,R_NUM = 1, 所以当 RK_NUM =R_NUM+1 时 , 是表一中 RK_NUM = 2 的记录
第三次 ,R_NUM = 2, 所以当 RK_NUM =R_NUM+1 时 , 是表一中 RK_NUM = 3 的记录
这样一直循环下去,直到找不到记录为止。
语句如下 :
WITH
R1
(
User_Name
,
City
,
Rk_Num
)
as
(
SELECT
User_Name
,
City
,
ROW_NUMBER
()
OVER
(
PARTITION
BY
User_Name
)
FROM
test
),
R2
(
User_Name
,
City
,
R_Num
)
as
(
SELECT
User_Name
,
CAST
(
City
AS
VARCHAR
(
100
)),
Rk_Num
from
R1
WHERE
Rk_Num
=
1
UNION ALL
SELECT
R2
.
User_Name
,
CAST
(
R2
.
City
||
'#'
||
R1
.
City
AS
VARCHAR
(
100
)),
R1
.
RK_Num
from
R2
,
R1
WHERE
R2
.
User_Name
=
R1
.
User_Name
and
R2
.
R_Num
+
1
=
R1
.
Rk_Num
)
SELECT
*
FROM
R2
结果 :
可以发现,根据名字分组后的每组 R_NUM 的最大值是需要的结果,那么可以这样写 WITH 后面的 select 语句 :
SELECT
a
.
User_name
,
a
.
city
FROM
R2
a
,(
SELECT
User_Name
,
max
(
R_Num
)
R_Num
from
R2
GROUP BY
User_Name
)
b
where
a
.
User_name
=
b
.
User_name
and
a
.
R_Num
=
b
.
R_Num
结果如下 :
发表评论
-
MySQL创建用户与授权
2015-12-08 19:19 1316一, 创建用户: 命令:CREATE USE ... -
数据库的拆分
2014-07-24 17:07 1118http://blog.csdn.net/bluishgl ... -
数据库事务隔离级别
2014-07-24 16:09 1047转自:http://singo107.iteye.com/b ... -
聚集索引和非聚集索引
2013-07-23 15:53 1864聚集索引和非聚集索引 聚集索引:表的物理存储按照 ... -
索引介绍
2013-07-23 14:39 1105按逻辑上来分: ... -
分区索引
2013-07-23 10:47 1249分区索引分为本地(loca ... -
反向键索引的原理和用途
2013-07-22 20:00 7291我们知道Oracle会自动为表的主键列建立索引,这个默认的 ... -
B树索引、位图索引和散列索引
2013-07-19 17:44 29820索引在数据结构上可以分为三种B树索引、位图索引和散列索引 ... -
SQLPLUS相关命令
2013-07-17 17:55 1115登录 sqlplus test/test123@MyD ... -
oracle trace文件查看
2013-07-17 17:51 1309CALL:每次SQL语句的处理都分成三个部分Parse:这步将 ... -
not in和not exists的区别
2013-05-27 13:59 2600先创建测试数据: create table test ... -
Latch (转)
2013-05-24 15:33 2495一. Latch 说明 1.1 Latc ... -
深度分析数据库的热点块问题(转)
2013-05-24 14:13 1439热点块的定义 ... -
join 条件在on和where 后的区别
2013-05-22 16:53 1271首先建两个表来测试下。 create table a( ... -
如何设计索引
2013-05-21 16:06 1629一个表建多少索引合适 ... -
重建索引
2013-05-20 23:30 1400关于索引重建,只需要记住一条: 如果它没坏,就不要 ... -
B+树索引
2013-05-20 16:10 147101.索引结构 1.1 B+树 ... -
ORACLE 循环
2012-10-12 18:39 12001、 Exit When 循环: ... -
高水位线
2012-10-08 16:38 1124所有的 oracle 段都有一个在段内容纳数据的上限 ... -
ORACLE直方图(转)
2012-10-08 13:42 1131一. 何谓直方图: ...
相关推荐
"SQL Server 2005 杂谈:公用表表达式(CTE)的递归调用" 本文主要介绍了 SQL Server 2005 中公用表表达式(CTE)的递归调用,用于解决树型结构数据的查询问题。CTE 是 SQL Server 2005 中的一种新的查询方式,它...
SQL Server 2005 中使用公用表表达式(CTE)简化嵌套 SQL SQL Server 2005 中的公用表表达式(CTE)是一种强大的工具,可以简化嵌套的 SQL 语句,提高代码的可维护性和性能。本文将介绍 CTE 的基本概念、语法和使用...
SQL Server中的公用表表达式(CTE,Common Table Expression)是一种强大的工具,它允许你在复杂的查询中定义一个临时的结果集,这个结果集可以被查询本身多次引用。CTE的使用非常灵活,尤其在处理递归关系时表现得...
SQL中的公用表表达式(CTE,Common Table Expression)是一种强大的工具,它允许你在执行单个查询时创建临时的结果集,这个结果集可以在后续的查询语句中被引用。CTE通过`WITH AS`短语来定义,它可以提升SQL语句的...
公用表表达式 (CTE) 可以认为是在单个 SELECT、INSERT、UPDATE、DELETE 或 CREATE VIEW 语句的执行范围内定义的临时结果集。 CTE 与派生表类似,具体表现在不存储为对象,并且只在查询期间有效。 与派生表的不同之处...
SQL Server 2005开始,我们可以直接通过CTE来支持递归查询,CTE即公用表表达式 公用表表达式(CTE),是一个在查询中定义的临时命名结果集将在from子句中使用它。每个CTE仅被定义一次(但在其作用域内可以被引用任意...
本文实例讲述了mysql8 公用表表达式CTE的使用方法。分享给大家供大家参考,具体如下: 公用表表达式CTE就是命名的临时结果集,作用范围是当前语句。 说白点你可以理解成一个可以复用的子查询,当然跟子查询还是有点...
公用表表达式(Common Table Expression,简称 CTE)是 SQL 语言中的一种功能强大的工具,尤其在处理层次结构数据时非常有用。它允许我们在查询中定义一个临时的结果集,这个结果集可以像常规表一样被引用,从而简化...
在SQL Server中,公用表表达式(Common Table Expression,简称CTE)是一种非常有用的查询构造,它可以临时定义一个结果集,然后在后续的查询中重复使用。CTE的一个强大特性是支持递归,这意味着它可以在自身的基础...
在SQL Server中,索引视图是一种特殊类型的视图,它的数据被物理地存储并维护着,这使得它们能够提供类似表的性能,特别是在处理大量数据和需要快速聚合操作时。这种视图的设计目的是为了提高查询性能,尤其是当查询...
其中,WITH CommonTableExpression 是可选的,表示公用表达式;select_expr 表示查询语句的表达式;LIMIT number 是可选的,用于限制查询结果返回的行数。 Hive 运算符 Hive 运算符用于对查询结果进行处理和计算。...
该项目是一款采用C#编写的轻量级高性能ORM设计源码,包含183个文件,其中包括130个C#源文件、21个T4模板文件、10个...此外,它还能处理复杂的SQL操作,如插入、选择、更新、连接、IN、EXISTS和公用表表达式(CTE)等。