记录下关于SQL Server的东西

CTE之所以与其他表表达式不同，是因为它支持递归查询：

定义一个递归CTE，至少需要两个查询（或者更多），第一个查询称为定位点成员（anchor member），第二个查询称为递归成员（recursive member），基本格式如下：

定位点成员只是返回有效关系结果表的查询，与用于非递归查询类似，定位点成员查询只被执行一次。

递归成员是一个引用了CTE名称的查询，对CTE名称的引用表示查询在一个执行序列中逻辑上的“前一个结果集”，第一次调用递归成员时，它表示的就是定位点成员的查询结果，之后调用递归时，引用CTE则代表前一次调用所返回的结果集。

递归成员没有显示的递归终止检查，递归成员会一直被重复调用，直到返回空的结果集或者超出了某种限制条件。

在返回的查询结果上，两个成员必须保持一直（列的属性）；

例如：

定位点成员对HR.Employees表中empid=2的结果进行查询，这个查询只执行一次；

递归成员则对CTE（前一次查询的结果集）和Employees表进行连接查询，返回前一次结果集中雇员的下级。直到返回结果为空，终止递归，默认情况下递归次数不超过100次，超过100次会终止，可以修改默认设置。

视图和内联表值函数（inline TVF）是一种可重用的表表达式，它们是保存在数据库中的，除非drop掉，否则属于永久可用的部分。

内联表值函数，支持输入参数，其他方面和视图很像，可以非正式的看成是支持输入参数的视图。

数据库透视转换：所谓透视转换（pivoting）就是把数据从行的状态转化为列的状态，当然对应的还有逆透视转换（unpivoting）：就是数据从列的状态转化为行的状态。

透视转换的标准解决方案就是通过一种非常直接的方式处理转换中的三个阶段，分组阶段通过group来实现，扩展阶段通过分别给每个目标指定case表达式来实现，这个要事先知道每个扩展目标的取值，并为每个值指定一个单独的case表达式，如果事先不知道要扩展的值，而且希望从数据中查询出这些值，就得使用动态SQL来构建查询字符串，并进行查询。

select empid,

sum(case when custid=‘A‘ then qty end) as A,

sum(case when custid=‘B‘ then qty end) as B,

sum(case when custid=‘C‘ then qty end) as C,

sum(case when custid=‘D‘ then qty end) as D

from dbo.Orders group by empid

在sql server 2005以后便可使用T_SQL中的pivot来做透视转换：

PIVOT运算符也是在查询的from子句的上下文中执行操作。它对某个源表或者表表达式进行操作、透视数据，再返回一个结果表。PIVOT运算符同样设计前面介绍的三个逻辑处理阶段（分组、扩展和聚合）和同样的透视转换元素，但使用的是不同的、SQL Server原生的（native）语法。

其语法格式为：

PIVOT后圆括号内指定聚集函数（比如sum）聚集元素、扩展元素以及目标列名称的列表，as后面可以为结果表指定一个别名。

select * from cte_order

pivot(sum (qty) for custid in(A,B,C,D)) as newtable

SQL Server 2008引入了merge语句，它能在一条语句中根据不同的逻辑进行不同的操作，比如可以根据不同的逻辑进行（delete，update，insert）；

Merge是基于连接语义的，在merge子句中指定目标表的名称，在using子句中指定源表的名称，通过on子句来定义和并条件。

在merge语句中，可以通过when matched then定义当找到匹配时执行的操作，通过when not matched then子句定义没有找到匹配时执行的操作。

例如：

merge into emp as a--确定目标表

using employees as b--确定源表

on a.firstname=b.firstname--匹配条件

when matched then--满足匹配条件时执行update（注意和update语句区别）

update set a.lastname=b.lastname,

a.mgrid=b.mgrid

when not matched then--未满足匹配条件时（注意和insert语句区别）

insert (firstname,lastname,mgrid) 

values(b.firstname,b.lastname,b.mgrid);--最后要加分号结束

在merge语句中也可以定义第三种字句when not matched by source，表示当目标表中的一个行，在来源表中没有行可以与之匹配的时候，和when not matched 区别在于：when not matched表示目标表中没有，而源表中有；when not matched by source 表示目标表中有，但是源表中没有。

也可以为子句增加and选项，当全部满足的时候，才会执行：

记录下关于SQL Server的东西,古老的榕树,5-wow.com