SQL Server中with as使用介绍

2023-08-09 17:22:04 80

一．WITHAS的含义

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

二．使用方法

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

select*fromperson.StateProvincewhereCountryRegionCodein
(selectCountryRegionCodefromperson.CountryRegionwhereNamelike'C%')

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

declare@ttable(CountryRegionCodenvarchar(3))
insertinto@t(CountryRegionCode)(selectCountryRegionCodefromperson.CountryRegionwhereNamelike'C%')

select*fromperson.StateProvincewhereCountryRegionCode
in(select*from@t)

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

下面是CTE的语法：

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

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

with
cras
(
selectCountryRegionCodefromperson.CountryRegionwhereNamelike'C%'
)

select*fromperson.StateProvincewhereCountryRegionCodein(select*fromcr)

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

在使用CTE时应注意如下几点：

1.CTE后面必须直接跟使用CTE的SQL语句（如select、insert、update等），否则，CTE将失效。如下面的SQL语句将无法正常使用CTE：

with
cras
(
selectCountryRegionCodefromperson.CountryRegionwhereNamelike'C%'
)
select*fromperson.CountryRegion--应将这条SQL语句去掉
--使用CTE的SQL语句应紧跟在相关的CTE后面--
select*fromperson.StateProvincewhereCountryRegionCodein(select*fromcr)

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

with
cte1as
(
select*fromtable1wherenamelike'abc%'
),
cte2as
(
select*fromtable2whereid>20
),
cte3as
(
select*fromtable3whereprice<100
)
selecta.*fromcte1a,cte2b,cte3cwherea.id=b.idanda.id=c.id

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

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

with
table1as
(
select*frompersonswhereage<30
)
select*fromtable1--使用了名为table1的公共表表达式
select*fromtable1--使用了名为table1的数据表

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

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

（1）COMPUTE或COMPUTEBY

（2）ORDERBY（除非指定了TOP子句）

（3）INTO

（4）带有查询提示的OPTION子句

（5）FORXML

（6）FORBROWSE

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

declare@snvarchar(3)
set@s='C%'
;--必须加分号
with
t_treeas
(
selectCountryRegionCodefromperson.CountryRegionwhereNamelike@s
)
select*fromperson.StateProvincewhereCountryRegionCodein(select*fromt_tree)

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

先看如下一个数据表（t_tree）：

上图显示了一个表中的数据，这个表有三个字段：id、node_name、parent_id。实际上，这个表中保存了一个树型结构，分三层：省、市、区。其中id表示当前省、市或区的id号、node_name表示名称、parent_id表示节点的父节点的id。
现在有一个需求，要查询出某个省下面的所有市和区（查询结果包含省）。如果只使用SQL语句来实现，需要使用到游标、临时表等技术。但在SQLServer2005中还可以使用CTE来实现。

从这个需求来看属于递归调用，也就是说先查出满足调价的省的记录，在本例子中的要查“辽宁省”的记录，如下：

id node_name parent_id

1 辽宁省 0

然后再查所有parent_id字段值为1的记录，如下：

id node_name parent_id

2 沈阳市 1

3 大连市 1

最后再查parent_id字段值为2或3的记录，如下：

id node_name parent_id

4 大东区 2

5 沈河区 2

6 铁西区 2

将上面三个结果集合并起来就是最终结果集。

上述的查询过程也可以按递归的过程进行理解，即先查指定的省的记录（辽宁省），得到这条记录后，就有了相应的id值，然后就进入了的递归过程，如下图所示。

从上面可以看出，递归的过程就是使用unionall合并查询结果集的过程，也就是相当于下面的递归公式：

resultset(n)=resultset(n-1)unionallcurrent_resultset

其中resultset(n)表示最终的结果集，resultset(n-1)表示倒数第二个结果集，current_resultset表示当前查出来的结果集，而最开始查询出“辽宁省”的记录集相当于递归的初始条件。而递归的结束条件是current_resultset为空。下面是这个递归过程的伪代码：

publicresultsetgetResultSet(resultset)
{
if(resultsetisnull)
{
current_resultset=第一个结果集（包含省的记录集）
将结果集的id保存在集合中
getResultSet(current_resultset)
}
current_resultset=根据id集合中的id值查出当前结果集
if(current_resultisnull)returnresultset
将当前结果集的id保存在集合中
returngetResultSet(resultsetunionallcurrent_resultset)
}

//获得最终结果集
resultset=getResultSet(null)

从上面的过程可以看出，这一递归过程实现起来比较复杂，然而CTE为我们提供了简单的语法来简化这一过程。
实现递归的CTE语法如下：

[WITH[,n]]
::=
expression_name[(column_name[,n])]
AS(
CTE_query_definition1 -- 定位点成员（也就是初始值或第一个结果集）
unionall
CTE_query_definition2 -- 递归成员
)

sql语句

with
districtas
(
--获得第一个结果集，并更新最终结果集
select*fromt_treewherenode_name=N'辽宁省'
unionall
--下面的select语句首先会根据从上一个查询结果集中获得的id值来查询parent_id
--字段的值，然后district就会变当前的查询结果集，并继续执行下面的select语句
--如果结果集不为null，则与最终的查询结果合并，同时用合并的结果更新最终的查
--询结果；否则停止执行。最后district的结果集就是最终结果集。
selecta.*fromt_treea,districtb
wherea.parent_id=b.id
)
select*fromdistrict
with
districtas
(
select*fromt_treewherenode_name=N'辽宁省'
unionall
selecta.*fromt_treea,districtb
wherea.parent_id=b.id
),
district1as
(
selecta.*fromdistrictawherea.idin(selectparent_idfromdistrict)
)
select*fromdistrict1

注：只有“辽宁省”和“沈阳市”有下子节点。

在定义和使用递归CTE时应注意如下几点：

1.递归CTE定义至少必须包含两个CTE查询定义，一个定位点成员和一个递归成员。可以定义多个定位点成员和递归成员；但必须将所有定位点成员查询定义置于第一个递归成员定义之前。所有CTE查询定义都是定位点成员，但它们引用CTE本身时除外。
2.定位点成员必须与以下集合运算符之一结合使用：UNIONALL、UNION、INTERSECT或EXCEPT。在最后一个定位点成员和第一个递归成员之间，以及组合多个递归成员时，只能使用UNIONALL集合运算符。
3.定位点成员和递归成员中的列数必须一致。
4.递归成员中列的数据类型必须与定位点成员中相应列的数据类型一致。
5.递归成员的FROM子句只能引用一次CTEexpression_name。
6.在递归成员的CTE_query_definition中不允许出现下列项：

（1）SELECTDISTINCT
（2）GROUPBY
（3）HAVING
（4）标量聚合
（5）TOP
（6）LEFT、RIGHT、OUTERJOIN（允许出现INNERJOIN）
（7）子查询
（8）应用于对CTE_query_definition中的CTE的递归引用的提示。

7.无论参与的SELECT语句返回的列的为空性如何，递归CTE返回的全部列都可以为空。
8.如果递归CTE组合不正确，可能会导致无限循环。例如，如果递归成员查询定义对父列和子列返回相同的值，则会造成无限循环。可以使用MAXRECURSION提示以及在INSERT、UPDATE、DELETE或SELECT语句的OPTION子句中的一个0到32,767之间的值，来限制特定语句所允许的递归级数，以防止出现无限循环。这样就能够在解决产生循环的代码问题之前控制语句的执行。服务器范围内的默认值是100。如果指定0，则没有限制。每一个语句只能指定一个MAXRECURSION值。
9.不能使用包含递归公用表表达式的视图来更新数据。
10.可以使用CTE在查询上定义游标。递归CTE只允许使用快速只进游标和静态（快照）游标。如果在递归CTE中指定了其他游标类型，则该类型将转换为静态游标类型。
11.可以在CTE中引用远程服务器中的表。如果在CTE的递归成员中引用了远程服务器，那么将为每个远程表创建一个假脱机，这样就可以在本地反复访问这些表。

下面是一些补充，很多参考价值

WITHAS短语，也叫做子查询部分（subqueryfactoring）
可以让你做很多事情，定义一个SQL片断，该SQL片断会被整个SQL语句所用到。

作为提供数据的部分。

代码例子：

withtempas
(selectID,Type_Name,Type_ID
fromT_Base_GoodsTypeast
wheret.Shop_ID=@shop_id
andType_ID=@Goods_TypeID
unionall
selectt1.ID,t1.Type_Name,t1.Type_ID
fromT_Base_GoodsTypeast1
innerjointemp
ont1.ParentType_ID=temp.Type_ID
wheret1.Shop_ID=@shop_id)
select*
from(selectStock_Amount,
S.StockWarn_Amount,
S.All_Amount,
G.Goods_ID,
G.Goods_Name,
G.Goods_Unit,
ROW_NUMBER()over(orderbyStock_Amountdesc)asrowid
fromT_IM_StockInfoasS
innerjoinT_Base_GoodsInfoASG
onS.Goods_ID=G.Goods_ID
innerjointemp
ontemp.Type_ID=G.Goods_TypeID
whereS.Shop_ID=@shop_id
ANDG.Shop_ID=@shop_id
andG.Goods_TypeID=temp.Type_ID
groupbyS.Stock_Amount,
S.All_Amount,
G.Goods_ID,
G.Goods_Name,
G.Goods_Unit,
S.StockWarn_Amount
HAVINGSUM(S.Stock_Amount)
sql循环（WITHAS短语也叫做子查询部分）
--表结构SELECTid,position,ParentidFROMop_client_sales_structure

WITHTEST_CTE
AS
(
SELECTid,position,Parentid,Cast(ParentidASNVARCHAR(4000))ASPATH
FROMop_client_sales_structureteam
WHEREParentid!=-1
UNIONALL
SELECTa.id,a.position,a.Parentid,
CTE.PATH+','+Cast(a.ParentidASNVARCHAR(4000))ASPATH
FROMop_client_sales_structurea
INNERJOINTEST_CTECTEONa.id=CTE.Parentid
)
SELECT*FROMTEST_CTEWHEREParentid=(SELECTidFROMop_client_sales_structureWHEREParentid=-1)
--限制递归次数
OPTION(MAXRECURSION10)
这篇文章就介绍到这，希望能帮助到你。
声明：本文内容来源于网络，版权归原作者所有，内容由互联网用户自发贡献自行上传，本网站不拥有所有权，未作人工编辑处理，也不承担相关法律责任。如果您发现有涉嫌版权的内容，欢迎发送邮件至：czq8825#qq.com（发邮件时，请将#更换为@）进行举报，并提供相关证据，一经查实，本站将立刻删除涉嫌侵权内容。

SQL Server中with as使用介绍

热门推荐

随机推荐