仓酷云

 找回密码
 立即注册
搜索
热搜: 活动 交友 discuz
查看: 1029|回复: 8
打印 上一主题 下一主题

[学习教程] 在sql server中使用with as完成递回功效

[复制链接]
深爱那片海 该用户已被删除
跳转到指定楼层
楼主
发表于 2015-1-16 14:07:10 | 只看该作者 回帖奖励 |倒序浏览 |阅读模式

马上注册,结交更多好友,享用更多功能,让你轻松玩转社区。

您需要 登录 才可以下载或查看,没有帐号?立即注册

x
线上或者测试环境经常出现的误操作总是让DBA同学那么闹心。在sqlserver2005之前,要完成递回功效对照贫苦,好比大概会要用光临时表与while语句来轮回。自sqlserver2005以后,新增了withas功效语法,即公用表达式(CTE),让递回完成起来变的复杂了。

本章我们次要演示怎样使用withas功效完成一个复杂的递回功效。
在这之前先看一下cte的语法:
[WITH<common_table_expression>[,...n]]
<common_table_expression>::=
expression_name[(column_name[,...n])]
AS
(CTE_query_definition)

参数申明
expression_name:
公用表表达式的无效标识符。expression_name必需与在统一WITH<common_table_expression>子句中界说的任何其他公用表表达式的称号分歧,但expression_name能够与基表或基视图的称号不异。在查询中对expression_name的任何援用城市利用公用表表达式,而不利用基工具。

column_name:
在公用表表达式中指定列名。在一个CTE界说中不同意呈现反复的称号。指定的列名数必需与CTE_query_definition了局会合列数婚配。只要在查询界说中为一切了局列都供应了分歧的称号时,列称号列表才是可选的。

CTE_query_definition:
指定一个其了局集添补公用表表达式的SELECT语句。除CTE不克不及界说另外一个CTE之外,CTE_query_definition的SELECT语句必需满意与创立视图时不异的请求。
假如界说了多个CTE_query_definition,则这些查询界说必需用以下一个汇合运算符连接起来:UNIONALL、UNION、EXCEPT或INTERSECT。

--入手下手实例演示--

先创立一个堆栈表,表名为Storage_Depository,该表有三个字段:DID(堆栈编号),DName(堆栈称号),PID(父堆栈编号).
经由过程如许一个复杂表,就能够将一切堆栈信息,经由过程DID与PID字段来创立一个树型布局。
创立表的sql语句:
CreatetableStorage_Depository
(
DIDvarchar(50)notnullprimarykey,
DNamevarchar(50)notnull,
PIDvarchar(50)null
)

然后往该表拔出演示数据:
insertintoStorage_Depository(DID,DName,PID)
selectA,A堆栈,null
unionall
selectA-1,A-1堆栈,A
unionall
selectA-2,A-2堆栈,A
unionall
selectA-1-1,A-1-1堆栈,A-1
unionall
selectB,B堆栈,null

从下面的数据能够看的出来,A的子仓为A-1与A-2仓,而A-1-1为A-1的子仓,B仓是一个自力的堆栈,与A仓平级。
上面,我们经由过程withas功效,查出A仓上面的一切子仓:
withw_Storage_Depositoryas
(
selectDID,DName,PIDfromStorage_DepositorywhereDID=A
unionall
selectA.DID,A.DName,A.PIDfromStorage_DepositoryA,w_Storage_DepositoryBwhereA.PID=B.DID
)
select*fromw_Storage_Depository

代码很冗长,也十分简单让人了解.

反过去,好比我们要查出A-1-1仓的一切下级仓,稍稍改一下下面的sql语句就能够了:
withw_Storage_Depositoryas
(
selectDID,DName,PIDfromStorage_DepositorywhereDID=A-1-1
unionall
selectA.DID,A.DName,A.PIDfromStorage_DepositoryA,
w_Storage_DepositoryBwhereA.DID=B.PID
)
select*fromw_Storage_Depository

很复杂吧,纯熟利用CTE后,会发明它会给我们的事情带来很年夜的便当,人人多多实习吧。恢复到之前的某个状态,是需要数据的。这数据可以是a)回滚步骤或者b)操作之前的数据状态原文。
莫相离 该用户已被删除
沙发
发表于 2015-1-18 12:04:35 来自手机 | 只看该作者
索引视k就有。但是2005对其效率作了一些改进但是schema.viewname的作用域真是太限制了它的应用面。还有一大堆的环境参数和种种限制都让人对它有点却步。
老尸 该用户已被删除
板凳
发表于 2015-1-23 16:48:18 | 只看该作者
可以动态传入参数,省却了动态SQL的拼写。
只想知道 该用户已被删除
地板
发表于 2015-1-31 18:55:56 | 只看该作者
无法深入到数据库系统层面去了解和探究
再现理想 该用户已被删除
5#
发表于 2015-2-6 22:42:25 | 只看该作者
再开发调试阶段和OLAP环境中,外键是可以建立的。新版本中加入了SETNULL和SETDEFAULT属性,能够提供能好的级联设置。
变相怪杰 该用户已被删除
6#
发表于 2015-2-19 05:44:32 | 只看该作者
在select语句中可以使用groupby子句将行划分成较小的组,然后,使用聚组函数返回每一个组的汇总信息,另外,可以使用having子句限制返回的结果集。
谁可相欹 该用户已被删除
7#
发表于 2015-3-6 13:32:27 | 只看该作者
作了些试验,发现使用CLR的存储过程或函数在达到一定的阀值的时候,系统性能会呈指数级下滑!这是非常危险的!只使用几个可能没有问题,当一旦大规模使用会造成严重的系统性能问题!
若相依 该用户已被删除
8#
发表于 2015-3-13 02:08:59 | 只看该作者
需要注意的一点,也是我使用过程中发现的一个问题。在建立function->schema->table后,如果在现有的分区表上建立没有显式声明的聚集索引时,分区表会自动变为非分区表。这一点很让我纳闷。
再见西城 该用户已被删除
9#
发表于 2015-3-20 10:06:21 | 只看该作者
这是一个不错的新特性。虽然索引的附加字段没有索引键值效率高,但是相对映射到数据表中效率还是提高了很多。我做过试验,在我的实验环境中会比映射到表中提高30%左右的效率。
您需要登录后才可以回帖 登录 | 立即注册

本版积分规则

QQ|Archiver|手机版|仓酷云 鄂ICP备14007578号-2

GMT+8, 2024-12-23 05:18

Powered by Discuz! X3.2

© 2001-2013 Comsenz Inc.

快速回复 返回顶部 返回列表