SQL Server2005中逻辑处理阶段的几个新元素

在SQLServer2005中引入了一些新的元素，包括：

1. 表运算符 apply、pivot、unpivot
2. 新的over子句
3. 新的集合操作except、intersect

Apply 包括cross apply 和 outer apply两种，区别同inner join 和outer join一样。实际上这个新的apply操作和join操作是同一个效果的，只是有下面两个不同：

1. 在进行join操作时，如果真实表a join 表值函数b的话，如果b的参数想引用参数a，这种情况是报错的。apply诞生是为了解决该问题。
2. join是第一步是进行俩表交叉链接，apply是针对左边表的每一行去应用右表表达式，然后得到结果

 

Pivot、unpivot 这两个东西是去年接触一个项目的时候才遇到的，当时奇怪这个东西究竟是做什么的？现在又回味这个东西，想来想去，觉得应该是针对一个特定问题的处理方式，这个问题如下描述：

在描述一类对象的一些属性时我们往往可以有两种方式设计表结构，一种是【对象、属性1、属性2...】这种方式，一种是【对象、属性、值】这种方式，显然两种方式都有着比较明显的优缺点：

前者：

缺点：扩展属性需要修改表结构　　优点：一条记录可以取到所有属性

后者：

缺点：取一个对象的属性信息需要返回多条记录　　优点：扩展属性值灵活方便，可以根据属性统计对象信息

 

所以在这两种方式上传统的也有一些办法进行行列的转换，正因为如此在2005中加入了pivot、unpivot这两个操作符方便我们处理。

举例说明：pivot 转换表

?

CREATE TABLE dbo.abc

    (

    a VARCHAR (20) NULL,

    b VARCHAR (20) NULL,

    v INT NULL

    )

GO

　　插入示例数据

?

INSERT INTO abc VALUES('李四', '英语', 40);

INSERT INTO abc VALUES('李四', '数学', 80);

INSERT INTO abc VALUES('李四', '语文', 50);

INSERT INTO abc VALUES('张三', '语文', 90);

INSERT INTO abc VALUES('张三', '数学', 60);

　　得到转置后的结果集

?

SELECT * FROM abc pivot(sum(v) FOR b IN (数学,语文,英语)) AS p

　　结果集如下

?

名字　　 数学　　 语文　　 英语<br>李四 80  50  40

张三  60  90  null

　　

Over子句在排名函数和聚合函数中的应用

注意：

1. 排名函数只能用于select选择列表中和order by子句中，即选择序号列、根据序号列进行排序
2. PARTITION BY 对行数据进行分区 Order by对行数据进行排序， 这两个操作可以用在over子句中

over子句的作用一个是对数据进行分组，但是这个分组和group by是不同的，PARTITION BY针对每行数据都返回一条记录，我们可以进行统计，如：求和、平均值等，然后再跟改行的原有数据进行一些操作、对比等。

over自己的另一个作用是根据分区产生序号，row_number() over(order by filed_list)  默认为根据整条记录产生序号，可以用分区操作为每个分区分别创建自己的序号

 

except、intersect 这两个操作从名字就可以看出，一个是求差、一个是求交集，同逻辑操作