微软认证考试70-461 Work with Data 数据处理 --27%比重--（2）

附注：微软认证考试70-461范围

1. Create Database Objects创建数据库对象 (24%)
2. Work with Data数据处理 (27%)
3. Modify Data数据修改 (24%)
4. Troubleshoot & Optimize故障排解及SQL优化 (25%)

本文是第二节Work with Data 数据处理 。

第一部分直通车

第二部分：Implement sub-queries. May include but not limited to: identify problematic elements in query plans; pivot and unpivot; apply operator; cte statement; with statement. 实现子查询。 可能包含但不仅限于：在查询计划中识别有问题的元素;pivot和unpivot;apply操作符；CTE语句结构；WITH语句结构。

PIVOT和UNPIVOT

PIVOT 通过将表达式某一列中的唯一值转换为输出中的多个列来旋转表值表达式，并在必要时对最终输出中所需的任何其余列值执行聚合。UNPIVOT 与 PIVOT 执行相反的操作，将表值表达式的列转换为列值。

基本准备：

create table TestPivot
(
    name varchar(50)
)
insert into testpivot(name)
select '微软认证'
union all
select '微软认证'
union all
select '微软认证考试'
union all
select '微软认证考试'
union all
select '乐可乐可的部落格'
union all
select '乐可乐可的部落格'
union all
select '乐可乐可的部落格'

用下面的查询当做一个表来操作

select name,count(*) as totalcount from testpivot
group by name

运行结果：

name                         totalcount
乐可乐可的部落格    3
微软认证                     2
微软认证考试            2

select 'totalcount' as name,[乐可乐可的部落格],[微软认证],[微软认证考试]
    from
    (
    select name,count(*) as totalcount from testpivot
    group by name
    ) a
    pivot
    (
        max(totalcount) for name in ([乐可乐可的部落格],[微软认证考试],[微软认证])
    ) b

运行结果：

name             乐可乐可的部落格    微软认证    微软认证考试
totalcount      3                                  2                  2

下面使用UNPIVOT将此结果集反转成初始结果集

select _name as name,_totalcount as totalcount
from
(
    select 'totalcount' as name,[乐可乐可的部落格],[微软认证],[微软认证考试]
    from
    (
    select name,count(*) as totalcount from testpivot
    group by name
    ) a
    pivot
    (
        max(totalcount) for name in ([乐可乐可的部落格],[微软认证考试],[微软认证])
    ) b
) d
unpivot
(
     _totalcount for _name  in([乐可乐可的部落格],[微软认证],[微软认证考试])
) c

运行结果：

name                         totalcount
乐可乐可的部落格    3
微软认证                     2
微软认证考试            2

参考：
http://msdn.microsoft.com/zh-cn/library/ms177410(v=sql.105).aspx

apply操作符

使用 APPLY 运算符可以为实现查询操作的外部表表达式返回的每个行调用表值函数。表值函数作为右输入，外部表表达式作为左输入。通过对右输入求值来获得左输入每一行的计算结果，生成的行被组合起来作为最终输出。APPLY 运算符生成的列的列表是左输入中的列集，后跟右输入返回的列的列表。

创建测试表：

create table test4Score
(
    test4id int,
    score int
)
insert into test4(name)
select '乐可乐可的部落格'
union all
select '微软考试认证'
union all
select '微软考试'

insert into test4score(test4id,score)
select 1,100
union all
select 1,90
union all
select 1,90
union all
select 1,80
union all
select 2,90
union all
select 2,82
union all
select 2,10

test4表中数据：

id    name
1    乐可乐可的部落格
2    微软考试认证
3    微软考试

test4score表中数据：

test4id    score
1              100
1                90
1                90
1                80
2                90
2                82
2                10

现在用APPLY操作符仅获取每个name的两个最高score记录：

select * from test4 a
cross apply
(
    select top 2 * from test4score where test4id=a.id order by score desc
) b

分析如下：

右输入-- select top 2 * from test4score where test4id=a.id order by score desc

左输入--select * from test4

右输入求值对左输入的每一行进行计算。

更进一步分析：

左输入第一行是1    乐可乐可的部落格

右输入计算左输入第一行id最高两个score记录得出：

id    test4id    score
1    1               100
3    1               90

组合行：

id    name                           test4id    score
1    乐可乐可的部落格    1               100
1    乐可乐可的部落格    1                90

以此类推，直至完成左输入所有行的计算。

结果如下：

id    name                           test4id    score
1    乐可乐可的部落格    1               100
1    乐可乐可的部落格    1                90
2    微软考试认证             2                90
2    微软考试认证             2                82

outer apply 类似于LEFT JOIN,

select * from test4 a
outer apply
(
    select top 2 * from test4score where test4id=a.id order by score desc
) b

id    name                           test4id    score
1    乐可乐可的部落格    1               100
1    乐可乐可的部落格    1                90
2    微软考试认证             2                90
2    微软考试认证             2                82
3    微软考试                       NULL        NULL

由于test4score表中没有'微软考试'的记录，所以用NULL真填充。

当然还有更多的方法来实现此需求，如下：

select b.name,a.score from(
select *,ROW_NUMBER()over(partition by test4id order by score desc) as rum from test4score
) a
inner join test4 b on b.id=a.test4id where rum < 3

结果：

name                        score
乐可乐可的部落格    100
乐可乐可的部落格    90
微软考试认证            90
微软考试认证            82

此方法是用前面介绍的ROW_NUMBER()和PARTITION BY来实现，详细请见：

微软认证考试70-461 Work with Data 数据处理 --27%比重--(1)

还有一种更古老的方法，但是必须给test4socre表添加标识列，新表结构如下：

create table test4Score
(
    id int identity(1,1),
    test4id int,
    score int
)

新数据：

id    test4id    score
1    1               100
2    1               90
3    1               90
4    1               80
5    2               90
6    2               82
7    2               10

用带子询询的SQL语句：

select a.name,b.score from test4 a inner join test4score b on a.id=b.test4id where b.id in
(
    select top 2 id from test4score where test4id=b.test4id order by score desc
)

结果：

name                          score
乐可乐可的部落格    100
乐可乐可的部落格    90
微软考试认证            90
微软考试认证            82

参考：http://msdn.microsoft.com/zh-cn/library/ms175156(v=sql.105).aspx

CTE语句结构

公用表表达式 (CTE) 可以认为是在单个 SELECT、INSERT、UPDATE、DELETE 或 CREATE VIEW 语句的执行范围内定义的临时结果集。CTE 与派生表类似，具体表现在不存储为对象，并且只在查询期间有效。与派生表的不同之处在于，CTE 可自引用，还可在同一查询中引用多次。

使用上面的test4表为例：

WITH TEST_CTE
AS
(
    select * from test4
)

此句创建了名为TEST_CTE的select * from test4的结果集。由于它不存储为对象，并且只在查询期间有效，所CTE和查询语句需要在一起执行：

WITH TEST_CTE
AS
(
    select * from test4
)
select * from TEST_CTE

结果集跟select * from test4结果集机同。

下面是指定列的CTE使用：

WITH TEST_CTE(id)
AS
(
    select id from test4
)

定义中的列需要与语句里面的列对应，见红色字体。

CTE可用于创建递归查询。

创建测试表并插入数据：

create table test5
(
    id int,
    name varchar(50),
    parentid int
)
insert into test5(id,name,parentid)
select 1,'父类1',0
union all
select 2,'父类2',0
union all
select 3,'父类3',0
union all
select 11,'子类11',1
union all
select 12,'子类12',1
union all
select 111,'子子类111',11
union all
select 22,'子类22',2
union all
select 222,'子子类222',22

结果：

id            name             parentid
1             父类1             0
2             父类2             0
3             父类3             0
11           子类11           1
12           子类12           1
111         子子类111     11
22           子类22           2
222         子子类222    22

使用CTE创建递归查询，获取父类1及所有其子类及子类的子类...：

with Test_Recursion(id,name,parentid,[level])
AS
(
    select id,name,parentid,0 from test5 where id =1--没有引用CTE自身必须放在第一个递归行之上
    union all--没有引用CTE自身的语句和第一个递归行之间必须用UNION ALL
    select a.id,a.name,a.parentid,b.[level]+1 from test5 as a join Test_Recursion as b on a.parentid=b.id--递归行
)
select * from Test_Recursion

结果：

id         name               parentid    level
1          父类1               0                   0
11        子类11             1                   1
12       子类12             1                   1
111      子子类111      11                  2

参考：http://msdn.microsoft.com/zh-cn/library/ms190766(v=sql.105).aspx

微软认证考试70-461第二节Work with Data数据处理 (27%)第二部分至此全部结束。

第三部分直通车

第四部分直通车

第五分部直通车