`

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

阅读更多

先看下面一个嵌套的查询语句:

select * from person.StateProvince where CountryRegionCode in 
         (select CountryRegionCode from person.CountryRegion where Name like 'C%')


    上面的查询语句使用了一个子查询。虽然这条SQL语句并不复杂,但如果嵌套的层次过多,会使SQL语句非常难以阅读和维护。因此,也可以使用表变量的方式来解决这个问题,SQL语句如下: 

declare @t table(CountryRegionCode nvarchar(3)) 
insert into @t(CountryRegionCode)  (select CountryRegionCode from person.CountryRegion where Name like 'C%') 

select * from person.StateProvince where CountryRegionCode 
                     in (select * from @t) 

 
    虽然上面的SQL语句要比第一种方式更复杂,但却将子查询放在了表变量@t中,这样做将使SQL语句更容易维护,但又会带来另一个问题,就是性能的损失。由于表变量实际上使用了临时表,从而增加了额外的I/O开销,因此,表变量的方式并不太适合数据量大且频繁查询的情况。为此,在SQL Server 2005中提供了另外一种解决方案,这就是公用表表达式(CTE),使用CTE,可以使SQL语句的可维护性,同时,CTE要比表变量的效率高得多。

    下面是CTE的语法: 

[ WITH <common_table_expression> [ ,n ] ] 
<common_table_expression>::= 
        expression_name [ ( column_name [ ,n ] ) ] 
    AS 
        ( CTE_query_definition ) 

 

    现在使用CTE来解决上面的问题,SQL语句如下: 

with 
cr as 
( 
    select CountryRegionCode from person.CountryRegion where Name like 'C%' 
) 

select * from person.StateProvince where CountryRegionCode in (select * from cr) 

     其中cr是一个公用表表达式,该表达式在使用上与表变量类似,只是SQL Server 2005在处理公用表表达式的方式上有所不同。

    在使用CTE时应注意如下几点:
1. CTE后面必须直接跟使用CTE的SQL语句(如select、insert、update等),否则,CTE将失效。如下面的SQL语句将无法正常使用CTE:

with 
cr as 
( 
    select CountryRegionCode from person.CountryRegion where Name like 'C%' 
) 
select * from person.CountryRegion  -- 应将这条SQL语句去掉 

 -- 使用CTE的SQL语句应紧跟在相关的CTE后面 --
select * from person.StateProvince where CountryRegionCode in (select * from cr)

2. CTE后面也可以跟其他的CTE,但只能使用一个with,多个CTE中间用逗号(,)分隔,如下面的SQL语句所示:

with 
cte1 as 
( 
    select * from table1 where name like 'abc%' 
), 
cte2 as 
( 
    select * from table2 where id > 20 
), 
cte3 as 
( 
    select * from table3 where price < 100 
) 
select a.* from cte1 a, cte2 b, cte3 c where a.id = b.id and a.id = c.id 

 3. 如果CTE的表达式名称与某个数据表或视图重名,则紧跟在该CTE后面的SQL语句使用的仍然是CTE,当然,后面的SQL语句使用的就是数据表或视图了,如下面的SQL语句所示:


--  table1是一个实际存在的表

with 
table1 as 
( 
    select * from persons where age < 30 
) 
select * from table1  --  使用了名为table1的公共表表达式 
select * from table1  --  使用了名为table1的数据表 

 4. CTE 可以引用自身,也可以引用在同一 WITH 子句中预先定义的 CTE。不允许前向引用。

5. 不能在 CTE_query_definition 中使用以下子句:

(1)COMPUTE 或 COMPUTE BY

(2)ORDER BY(除非指定了 TOP 子句)

(3)INTO

(4)带有查询提示的 OPTION 子句

(5)FOR XML

(6)FOR BROWSE

6. 如果将 CTE 用在属于批处理的一部分的语句中,那么在它之前的语句必须以分号结尾,如下面的SQL所示: 

declare @s nvarchar(3) 
set @s = 'C%' 
;  -- 必须加分号 
with 
t_tree as 
( 
    select CountryRegionCode from person.CountryRegion where Name like @s 
) 
select * from person.StateProvince where CountryRegionCode in (select * from t_tree) 

     CTE除了可以简化嵌套SQL语句外,还可以进行递归调用,关于这一部分的内容将在下一篇文章中介绍。

 

公用表表达式(CTE)的递归调用

http://blog.csdn.net/zzuyongp/archive/2009/05/11/4164951.aspx

 

四个排名函数

http://blog.csdn.net/zzuyongp/archive/2009/05/11/4164891.aspx

 

分享到:
评论

相关推荐

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

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

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

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

    Microsoft_SQL_Server_2005技术内幕:T-SQL查询.pdf

    本书是Inside Microsoft SQL Server 2005系列四本著作中的一本。它详细介绍了T-SQL的内部体系结构,包含了非常全面的编程参考,提供了使用Transact-SQL(T-SQL)的专家级指导,囊括了非常全面的编程参考,揭示了基于...

    (第二卷)Microsoft SQL Server 2008技术内幕:T-SQL语言基础

    5.2 公用表表达式(CTE) 5.3 视图 5.4 内联表值函数 5.5 APPLY运算符 5.6 总结 5.7 练习 5.8 解决方案 第6章 集合运算 6.1 UNION(并集)集合运算 6.2 INTERSECT(交集)集合运算 6.3 EXCEPT...

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

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

    Microsoft SQL Server 2005技术内幕:T-SQL查询

     5.2 公用表表达式(CTE)  5.3 视图  5.4 内联表值函数  5.5 APPLY运算符  5.6 总结  5.7 练习  5.8 解决方案 第6章 集合运算  6.1 UNION(并集)集合运算  6.2 INTERSECT(交集)集合运算  ...

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

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

    sql server 2005脚本

    SQL Server 2005对T-SQL进行了扩展,添加了新的语法元素和函数,如窗口函数(OVER子句)、公用表表达式(CTE)、递归查询以及WITH关键字。这些功能使得复杂的数据处理和分析变得更加高效。 2. **存储过程和触发器*...

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

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

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

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

    Microsoft SQL Server 2008技术内幕:T-SQL查询(英文版)

    - **表表达式**:探讨CTE(公用表表达式)和派生表的概念及其应用场景。 - **排名函数**:学习使用ROW_NUMBER()、RANK()等函数来进行数据排序和分组。 - **数据聚合**:掌握GROUP BY、HAVING子句以及聚合函数如...

    SQL2005查询工具 SQLServer2005_SSMSEE

    1. **T-SQL增强**:SQL Server 2005引入了新的T-SQL语言特性,如Cursors的改进、窗口函数、 Common Table Expressions (CTE)、Table Value Parameters 和 PIVOT/UNPIVOT操作等,使得查询编写更为灵活和强大。...

    (第一卷)Microsoft.SQL.Server.2008技术内幕:T-SQL语言基础

    5.2 公用表表达式(CTE) 5.3 视图 5.4 内联表值函数 5.5 APPLY运算符 5.6 总结 5.7 练习 5.8 解决方案 第6章 集合运算 6.1 UNION(并集)集合运算 6.2 INTERSECT(交集)集合运算 6.3 EXCEPT(差集)...

    Pro SQL Server 2005 (2006)

    2. **Transact-SQL (T-SQL)增强**:SQL Server 2005扩展了T-SQL语言,增加了新的函数、操作符和语句,如窗口函数、公用表表达式(CTE)和嵌套触发器,提高了开发人员编写复杂查询的灵活性。 3. **Integration ...

    sqlserver 2005 使用CTE 实现递归

    在 SQL Server 2005 中,使用公共表表达式(Common Table Expression, CTE)实现递归是一种高效且直观的方式。递归 CTE 通常由两部分构成:定位成员(Anchor Member, AM)和递归成员(Recursive Member, RM)。 1. ...

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

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

    SQL Server 2005 Beta 2 Transact-SQL 增强功能

    SQL Server 2005 Beta 2带来了对Transact-SQL的显著增强,这些改进主要集中在提升查询表达能力、数据完整性(DRI)支持、性能优化以及错误处理方面。以下是对这些增强功能的详细说明: 1. **排序函数**: SQL ...

Global site tag (gtag.js) - Google Analytics