SQL SERVER中apply操作符

apply操作符

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

基础准备

创建测试表：

create table test4(    id int identity(1,1),    name varchar(100))create table test4Score(    test4id int,    score int)insert into test4(name)select 'LeeWhoeeUniversity'union allselect 'LeeWhoee'union allselect 'DePaul'insert into test4score(test4id,score)select 1,100union allselect 1,90union all select 1,90union all select 1,80union all select 2,90union all select 2,82union all select 2,10

test4表中数据：

id    name
1    LeeWhoeeUniversity
2    LeeWhoee
3    DePaul

test4score表中数据：

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

APPLY

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

select * from test4 across 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    LeeWhoeeUniversity

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

id    test4id    score
1    1               100
3    1               90

组合行：

id    name                           test4id    score
1    LeeWhoeeUniversity    1               100
1    LeeWhoeeUniversity    1                90

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

结果如下：

id    name                           test4id    score
1    LeeWhoeeUniversity    1               100
1    LeeWhoeeUniversity    1                90
2    LeeWhoee                     2                90
2    LeeWhoee                     2                82

OUTER APPLY

outer apply 类似于LEFT JOIN,

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

id    name                           test4id    score
1    LeeWhoeeUniversity    1               100
1    LeeWhoeeUniversity    1                90
2    LeeWhoee                     2                90
2    LeeWhoee                     2                82
3    DePaul                       NULL        NULL

由于test4score表中没有'DePaul'的记录，所以用NULL值填充。

当然还有更多的方法来实现此需求，如使用排名函数ROW_NUMBER：

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
LeeWhoeeUniversity    100
LeeWhoeeUniversity    90
LeeWhoee            90
LeeWhoee            82

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

SQL SERVER排名函数RANK，DENSE_RANK，NTILE，ROW_NUMBER

还有一种更古老的方法，但是必须给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
LeeWhoeeUniversity    100
LeeWhoeeUniversity    90
LeeWhoee                     90
LeeWhoee                     82