SQL SERVER中apply操作符

 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