一.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语句如下:
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 2005 中的公用表表达式(CTE)是一种强大的工具,可以简化嵌套的 SQL 语句,提高代码的可维护性和性能。本文将介绍 CTE 的基本概念、语法和使用方法,并与传统的子查询和表变量相比,看看 CTE 是如何简化...
1. **简化复杂的嵌套查询**:当一个查询中包含多个子查询时,使用`WITH AS`可以将这些子查询定义为CTE,从而让整个查询更加清晰易懂。 2. **重复使用子查询结果**:如果一个查询中多次使用同一个子查询的结果,那么...
通过以上的介绍和示例,我们可以看出`WITH...AS`在SQL查询中是非常实用的一个特性,不仅可以简化查询语句,还可以在某些场景下提升查询性能。熟练掌握并运用CTE可以帮助我们更好地管理和操作数据库中的数据。
SQL(Structured Query ...随着经验的增长,还可以深入学习更复杂的SQL特性,如窗口函数、分区、索引优化等,以提高查询性能和解决更复杂的业务需求。在Java开发中,熟悉SQL和JDBC将使你能够在各种项目中更加得心应手。
总的来说,`WITH AS`在SQL中是一个强大且灵活的工具,它能够简化复杂的查询,提高代码可读性,并在某些情况下提升查询性能。正确理解和使用CTE,可以帮助我们编写出更加高效和易于维护的SQL语句。
### Oracle高级SQL培训与讲解——WITH...综上所述,WITH子句是一种非常实用且强大的SQL特性,它不仅可以帮助简化复杂的查询语句,还能有效提升查询性能。熟练掌握WITH子句的使用方法,对于提高数据库查询技能至关重要。
- **规范要求**:在进行表关联时,应当使用表别名来简化SQL语句,提高可读性。 - **示例**: ```sql SELECT E.NAME, D.DEPARTMENT_NAME FROM EMPLOYEE E JOIN DEPARTMENT D ON E.DEPARTMENT_ID = D.ID; ``` ##...
在处理多层嵌套查询时,使用WITH AS语句可以简化查询结构,使其更易于理解和维护。 #### 五、示例分析 为了更好地理解WITH AS语句的实际应用,下面通过一个具体的例子进行说明。假设我们有一个DB2数据库环境,并...
标题与描述均指向了一个主题——Oracle高级SQL培训与讲解,特别是关于WITH子句的深入...总之,掌握了WITH子句的使用,不仅能够提升SQL查询的性能,还能使代码更加整洁、高效,是Oracle高级SQL技能中不可或缺的一部分。
1. **简化多层嵌套查询**:使用CTE可以将复杂的嵌套查询拆分为多个步骤,每一步作为一个单独的CTE,这样不仅提高了查询的可读性,还便于调试和维护。 2. **数据聚合**:CTE可以用来聚合数据,例如计算总和、平均值等...
总的来说,这个小工具提供了一个高效的方法来管理SQL文件中的表名,简化了数据库相关工作的流程,特别是在处理大量SQL脚本时,能显著提高工作效率。对于那些经常需要处理SQL文件的人来说,这样的工具无疑是十分有用...
这种情况下,使用子查询或CTE可以简化代码并提高可读性。 CTE是SQL Server 2005引入的新特性,它允许定义一个临时的结果集,可以在后续的查询中重复使用。CTE的语法格式如下: ```sql WITH cte_name AS (cte_query...
- **EXISTS、ANY、ALL的使用**:这些关键字用于简化查询逻辑,提高查询效率。 #### 第三部分:数据操作与管理 **知识点八:操作数据** - **数据操作语句**:包括INSERT(插入数据)、UPDATE(更新数据)、DELETE...
存储过程是数据库设计和应用开发中的关键组件,可以提升性能、增强安全性并简化代码维护。下面将详细介绍SQL Server存储过程的相关知识点。 一、存储过程的类型 1. **系统存储过程**:由Microsoft SQL Server提供,...
- 使用Oracle提供的内置函数来简化代码并提高性能。 - 示例: ```plsql select nvl(col1, 0) as col1 from tab1; ``` ##### 3.9 推荐使用DECODE - 使用`decode`函数来替代复杂的`if-else`结构。 - 示例: ```...
在本文中,我们将探讨这三种临时表的使用场景,以及`INSERT INTO`、`SELECT INTO`和`WITH AS`语句的应用。 1. **INSERT INTO @NewTable1 SELECT...** 这种方式创建的是一个表变量,通常用于在存储过程或批处理内部...
T-SQL (Transact-SQL) 是 Microsoft SQL Server 使用的一种扩展版本的 SQL 语言,它提供了额外的功能来增强 SQL 的功能,使开发者能够更高效地管理和操作数据库。 - **DML (数据操作语言 Data Manipulation ...