`
liulanghan110
  • 浏览: 1075589 次
  • 性别: Icon_minigender_1
  • 来自: 武汉
社区版块
存档分类
最新评论

公用表表达式

阅读更多


 公用表表达式
(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

结果如下 :



 

  • 大小: 11.3 KB
  • 大小: 9.2 KB
  • 大小: 11.1 KB
  • 大小: 7.1 KB
  • 大小: 15.3 KB
  • 大小: 7.7 KB
分享到:
评论

相关推荐

    [sql server]SQL Server2005杂谈(2):公用表表达式(CTE)的递归调用.doc

    "SQL Server 2005 杂谈:公用表表达式(CTE)的递归调用" 本文主要介绍了 SQL Server 2005 中公用表表达式(CTE)的递归调用,用于解决树型结构数据的查询问题。CTE 是 SQL Server 2005 中的一种新的查询方式,它...

    [sql server] SQL Server2005杂谈(1):使用公用表表达式(CTE)简化嵌套SQL

    SQL Server 2005 中使用公用表表达式(CTE)简化嵌套 SQL SQL Server 2005 中的公用表表达式(CTE)是一种强大的工具,可以简化嵌套的 SQL 语句,提高代码的可维护性和性能。本文将介绍 CTE 的基本概念、语法和使用...

    SQL Server 公用表表达式(CTE)实现递归的方法

    SQL Server中的公用表表达式(CTE,Common Table Expression)是一种强大的工具,它允许你在复杂的查询中定义一个临时的结果集,这个结果集可以被查询本身多次引用。CTE的使用非常灵活,尤其在处理递归关系时表现得...

    关于SQL中CTE(公用表表达式)(Common Table Expression)的总结

    SQL中的公用表表达式(CTE,Common Table Expression)是一种强大的工具,它允许你在执行单个查询时创建临时的结果集,这个结果集可以在后续的查询语句中被引用。CTE通过`WITH AS`短语来定义,它可以提升SQL语句的...

    SQL2005 学习笔记 公用表表达式(CTE)

    公用表表达式 (CTE) 可以认为是在单个 SELECT、INSERT、UPDATE、DELETE 或 CREATE VIEW 语句的执行范围内定义的临时结果集。 CTE 与派生表类似,具体表现在不存储为对象,并且只在查询期间有效。 与派生表的不同之处...

    SqlServer使用公用表表达式(CTE)实现无限级树形构建

    SQL Server 2005开始,我们可以直接通过CTE来支持递归查询,CTE即公用表表达式 公用表表达式(CTE),是一个在查询中定义的临时命名结果集将在from子句中使用它。每个CTE仅被定义一次(但在其作用域内可以被引用任意...

    mysql8 公用表表达式CTE的使用方法实例分析

    本文实例讲述了mysql8 公用表表达式CTE的使用方法。分享给大家供大家参考,具体如下: 公用表表达式CTE就是命名的临时结果集,作用范围是当前语句。 说白点你可以理解成一个可以复用的子查询,当然跟子查询还是有点...

    公用表表达式可查找所有相关节点

    公用表表达式(Common Table Expression,简称 CTE)是 SQL 语言中的一种功能强大的工具,尤其在处理层次结构数据时非常有用。它允许我们在查询中定义一个临时的结果集,这个结果集可以像常规表一样被引用,从而简化...

    sql server使用公用表表达式CTE通过递归方式编写通用函数自动生成连续数字和日期

    在SQL Server中,公用表表达式(Common Table Expression,简称CTE)是一种非常有用的查询构造,它可以临时定义一个结果集,然后在后续的查询中重复使用。CTE的一个强大特性是支持递归,这意味着它可以在自身的基础...

    SQL Server中用索引视图查看性能状况

    在SQL Server中,索引视图是一种特殊类型的视图,它的数据被物理地存储并维护着,这使得它们能够提供类似表的性能,特别是在处理大量数据和需要快速聚合操作时。这种视图的设计目的是为了提高查询性能,尤其是当查询...

    大数据学习:Hive数据查询语言.pdf

    其中,WITH CommonTableExpression 是可选的,表示公用表达式;select_expr 表示查询语句的表达式;LIMIT number 是可选的,用于限制查询结果返回的行数。 Hive 运算符 Hive 运算符用于对查询结果进行处理和计算。...

    基于C#的轻量级高性能ORM设计源码,支持分页、事务、模型映射等特性

    该项目是一款采用C#编写的轻量级高性能ORM设计源码,包含183个文件,其中包括130个C#源文件、21个T4模板文件、10个...此外,它还能处理复杂的SQL操作,如插入、选择、更新、连接、IN、EXISTS和公用表表达式(CTE)等。

Global site tag (gtag.js) - Google Analytics