`
zzc1684
  • 浏览: 1220864 次
  • 性别: Icon_minigender_1
  • 来自: 广州
文章分类
社区版块
存档分类
最新评论

使用WITH AS提高性能简化嵌套SQL

阅读更多

一.WITH AS的含义

    WITH AS短语,也叫做子查询部分(subquery factoring),可以让你做很多事情,定义一个SQL片断,该SQL片断会
被整个SQL语句所用到。有的时候,是为了让SQL语句的可读性更高些,也有可能是在UNION ALL的不同部分,作为提供数
据的部分。
特别对于UNION ALL比较有用。因为UNION ALL的每个部分可能相同,但是如果每个部分都去执行一遍的话,则成本太高,
所以可以使用WITH AS短语,则只要执行一遍即可。如果WITH AS短语所定义的表名被调用两次以上,则优化器会自动将
WITH AS短语所获取的数据放入一个TEMP表里,如果只是被调用一次,则不会。而提示materialize则是强制将WITH AS
短语里的数据放入一个全局临时表里。很多查询通过这种方法都可以提高速度。

二.使用方法

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

select*from person.StateProvince where CountryRegionCode in 
         (select CountryRegionCode from person.CountryRegion where Name like'C%') 
上面的查询语句使用了一个子查询。虽然这条SQL语句并不复杂,但如果嵌套的层次过多,会使SQL语句非常难以阅
读和维护。因此,也可以使用表变量的方式来解决这个问题。

SQL语句如下:

declare@ttable(CountryRegionCode nvarchar(3)) 
insertinto@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@snvarchar(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) 
分享到:
评论

相关推荐

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

    SQL Server 2005 中的公用表表达式(CTE)是一种强大的工具,可以简化嵌套的 SQL 语句,提高代码的可维护性和性能。本文将介绍 CTE 的基本概念、语法和使用方法,并与传统的子查询和表变量相比,看看 CTE 是如何简化...

    sql中with as的应用整理

    1. **简化复杂的嵌套查询**:当一个查询中包含多个子查询时,使用`WITH AS`可以将这些子查询定义为CTE,从而让整个查询更加清晰易懂。 2. **重复使用子查询结果**:如果一个查询中多次使用同一个子查询的结果,那么...

    sql里面 with...as 是什么意思?如何使用?

    通过以上的介绍和示例,我们可以看出`WITH...AS`在SQL查询中是非常实用的一个特性,不仅可以简化查询语句,还可以在某些场景下提升查询性能。熟练掌握并运用CTE可以帮助我们更好地管理和操作数据库中的数据。

    getting started with sql

    SQL(Structured Query ...随着经验的增长,还可以深入学习更复杂的SQL特性,如窗口函数、分区、索引优化等,以提高查询性能和解决更复杂的业务需求。在Java开发中,熟悉SQL和JDBC将使你能够在各种项目中更加得心应手。

    sql with as用法详解

    总的来说,`WITH AS`在SQL中是一个强大且灵活的工具,它能够简化复杂的查询,提高代码可读性,并在某些情况下提升查询性能。正确理解和使用CTE,可以帮助我们编写出更加高效和易于维护的SQL语句。

    Oracle高级SQL培训与讲解

    ### Oracle高级SQL培训与讲解——WITH...综上所述,WITH子句是一种非常实用且强大的SQL特性,它不仅可以帮助简化复杂的查询语句,还能有效提升查询性能。熟练掌握WITH子句的使用方法,对于提高数据库查询技能至关重要。

    DBA对Oracle SQL编写规范的总结

    - **规范要求**:在进行表关联时,应当使用表别名来简化SQL语句,提高可读性。 - **示例**: ```sql SELECT E.NAME, D.DEPARTMENT_NAME FROM EMPLOYEE E JOIN DEPARTMENT D ON E.DEPARTMENT_ID = D.ID; ``` ##...

    软件开发高手须掌握的4大SQL精髓语句(三)

    在处理多层嵌套查询时,使用WITH AS语句可以简化查询结构,使其更易于理解和维护。 #### 五、示例分析 为了更好地理解WITH AS语句的实际应用,下面通过一个具体的例子进行说明。假设我们有一个DB2数据库环境,并...

    Oracle高级SQL培训与讲解.doc

    标题与描述均指向了一个主题——Oracle高级SQL培训与讲解,特别是关于WITH子句的深入...总之,掌握了WITH子句的使用,不仅能够提升SQL查询的性能,还能使代码更加整洁、高效,是Oracle高级SQL技能中不可或缺的一部分。

    sql写法_CTE

    1. **简化多层嵌套查询**:使用CTE可以将复杂的嵌套查询拆分为多个步骤,每一步作为一个单独的CTE,这样不仅提高了查询的可读性,还便于调试和维护。 2. **数据聚合**:CTE可以用来聚合数据,例如计算总和、平均值等...

    解析SQL提取表名并输出文本文件

    总的来说,这个小工具提供了一个高效的方法来管理SQL文件中的表名,简化了数据库相关工作的流程,特别是在处理大量SQL脚本时,能显著提高工作效率。对于那些经常需要处理SQL文件的人来说,这样的工具无疑是十分有用...

    SqlServer 2005 T-SQL Query 学习笔记(1)

    这种情况下,使用子查询或CTE可以简化代码并提高可读性。 CTE是SQL Server 2005引入的新特性,它允许定义一个临时的结果集,可以在后续的查询中重复使用。CTE的语法格式如下: ```sql WITH cte_name AS (cte_query...

    21天迅速学会sql(比较完整的sql学习手册)

    - **EXISTS、ANY、ALL的使用**:这些关键字用于简化查询逻辑,提高查询效率。 #### 第三部分:数据操作与管理 **知识点八:操作数据** - **数据操作语句**:包括INSERT(插入数据)、UPDATE(更新数据)、DELETE...

    sqlsever存储过程

    存储过程是数据库设计和应用开发中的关键组件,可以提升性能、增强安全性并简化代码维护。下面将详细介绍SQL Server存储过程的相关知识点。 一、存储过程的类型 1. **系统存储过程**:由Microsoft SQL Server提供,...

    Oracle 开发DBA SQL编写规范

    - 使用Oracle提供的内置函数来简化代码并提高性能。 - 示例: ```plsql select nvl(col1, 0) as col1 from tab1; ``` ##### 3.9 推荐使用DECODE - 使用`decode`函数来替代复杂的`if-else`结构。 - 示例: ```...

    Mssql 三种临时表的使用场景 insert into 与 select into与With as.docx

    在本文中,我们将探讨这三种临时表的使用场景,以及`INSERT INTO`、`SELECT INTO`和`WITH AS`语句的应用。 1. **INSERT INTO @NewTable1 SELECT...** 这种方式创建的是一个表变量,通常用于在存储过程或批处理内部...

    T-SQL编程入门(SQL Server)

    T-SQL (Transact-SQL) 是 Microsoft SQL Server 使用的一种扩展版本的 SQL 语言,它提供了额外的功能来增强 SQL 的功能,使开发者能够更高效地管理和操作数据库。 - **DML (数据操作语言 Data Manipulation ...

Global site tag (gtag.js) - Google Analytics