仓酷云

标题: 在sql server中使用with as完成递回功效 [打印本页]

作者: 深爱那片海 时间: 2015-1-16 14:07
标题: 在sql server中使用with as完成递回功能
线上或者测试环境经常出现的误操作总是让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
索引视k就有。但是2005对其效率作了一些改进但是schema.viewname的作用域真是太限制了它的应用面。还有一大堆的环境参数和种种限制都让人对它有点却步。

作者: 老尸 时间: 2015-1-23 16:48
可以动态传入参数，省却了动态SQL的拼写。

作者: 只想知道 时间: 2015-1-31 18:55
无法深入到数据库系统层面去了解和探究

作者: 再现理想 时间: 2015-2-6 22:42
再开发调试阶段和OLAP环境中，外键是可以建立的。新版本中加入了SETNULL和SETDEFAULT属性，能够提供能好的级联设置。

作者: 变相怪杰 时间: 2015-2-19 05:44
在select语句中可以使用groupby子句将行划分成较小的组，然后，使用聚组函数返回每一个组的汇总信息，另外，可以使用having子句限制返回的结果集。

作者: 谁可相欹 时间: 2015-3-6 13:32
作了些试验，发现使用CLR的存储过程或函数在达到一定的阀值的时候，系统性能会呈指数级下滑！这是非常危险的！只使用几个可能没有问题，当一旦大规模使用会造成严重的系统性能问题！

作者: 若相依 时间: 2015-3-13 02:08
需要注意的一点，也是我使用过程中发现的一个问题。在建立function->schema->table后，如果在现有的分区表上建立没有显式声明的聚集索引时，分区表会自动变为非分区表。这一点很让我纳闷。

作者: 再见西城 时间: 2015-3-20 10:06
这是一个不错的新特性。虽然索引的附加字段没有索引键值效率高，但是相对映射到数据表中效率还是提高了很多。我做过试验，在我的实验环境中会比映射到表中提高30%左右的效率。

欢迎光临仓酷云 (http://ckuyun.com/)