SQL SERVER中apply操作符

您现在的位置：首页>教程>编程开发>mssql数据库 > SQL SERVER中apply操作符 SQL SERVER中apply操作符 感谢 3lian8 的投递 时间：2014-03-10 来源：三联教程 apply操作符 使用 APPLY 运算符可以为实现查询操作的外部表表达式返回的每个行调用表值函数。表值函

  您现在的位置：首页 > 教程 > 编程开发 > mssql数据库 > SQL SERVER中apply操作符

SQL SERVER中apply操作符

感谢 3lian8 的投递 时间：2014-03-10 来源：三联教程　

 apply操作符

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

基础准备

创建测试表：

?

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

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 all

select 'LeeWhoee'

union all

select 'DePaul'

 

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 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记录：

 

?

1

2

3

4

5

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 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,

?

1

2

3

4

5

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 LeeWhoeeUniversity 1 100
1 LeeWhoeeUniversity 1 90
2 LeeWhoee 2 90
2 LeeWhoee 2 82
3 DePaul NULL NULL

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

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

 

?

1

2

3

4

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表添加标识列，新表结构如下：

 

?

1

2

3

4

5

6

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语句：

 

?

1

2

3

4

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

相关文章

标签:

[返回三联首页] [返回mssql数据库栏目] / [加入三联文集]