Apply operator in SQL SERVER

Apply operator in SQL SERVER
Apply Operator

You can use the APPLY operator to call the table value function for each row returned by the External table expression that implements the query operation. The table value function is used as the right input, and the External table expression is used as the left input. Evaluate the right input to obtain the calculation result of each row in the left input. The generated rows are combined for final output. The list of columns generated by the APPLY operator is the column set in the left input, followed by the list of columns returned by the right input.

Basic preparation

Create a test table:

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

Data in table test4:

Id name
1 LeeWhoeeUniversity
2 LeeWhoee
3 DePaul

Data in the test4score table:

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

APPLY

Now, use the APPLY operator to obtain only two highest score records for each name:

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

The analysis is as follows:

Right input -- select top 2 * from test4score where test4id = a. id order by score desc

Left input -- select * from test4

Evaluate the right input to calculate each row of the Left input.

Further analysis:

The first line of the Left input is1 LeeWhoeeUniversity

Right input calculation left input first line id up to two score records are obtained:

Id test4id score
1 100
3 1 90

Combination row:

Id name test4id score
1 LeeWhoeeUniversity 1 100
1 LeeWhoeeUniversity 1 90

And so on until the calculation of all rows input on the left is completed.

The result is as follows:

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

OUTER APPLY

Outer apply is similar to 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

Because the test4score table does not have'DePaul', So use NULL value to fill.

Of course there are more ways to achieve this, such as using the rank function 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

Result:

Name score
LeeWhoeeUniversity 100
LeeWhoeeUniversity 90
LeeWhoee 90
LeeWhoee 82

This method is implemented using ROW_NUMBER () and partition by described earlier. For details, see:

SQL SERVER ranking function RANK, DENSE_RANK, NTILE, ROW_NUMBER

There is also an older method, but the ID column must be added to the test4socret table. The structure of the new table is as follows:

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

New data:

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

SQL statement to be queried using a tape:

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)

Result:

Name score
LeeWhoeeUniversity 100
LeeWhoeeUniversity 90
LeeWhoee 90
LeeWhoee 82