SQL SERVER中apply操作符

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