Apply operator in SQL Server

Apply operator

Use the APPLY operator to call a table-valued function for each row returned by an external table expression that implements a query operation. The table-valued function is the right input, and the outer table expression is left-input. The resulting row is combined as the final output by evaluating the right input for each row in the left input. The list of columns generated by the APPLY operator is the set of columns in the left input, followed by the list of columns returned by the right input.

Basic Preparation

To create a test table:

?

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 (MB)) CREATE TABLE Test4score (test4id int, Scor e 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 S Elect 2,90 UNION ALL Select 2,82 UNION ALL Select 2,10

TEST4 Data in table:

ID Name
1 leewhoeeuniversity
2 Leewhoee
3 DePaul

Test4score Data in table:

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

APPLY

Now use the Apply operator to get only the two highest score records for each name:

?

1 2 3 4 5

SELECT * from Test4 a cross apply (select top 2 * to 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

The right input evaluates to each row entered on the left.

Further analysis:

Left input first line is 1 leewhoeeuniversity

Right input calculation left enter first line ID up to two score records:

ID TEST4ID Score
1 1 100
3 1 90

Combine lines:

ID Name Test4id Score
1 leewhoeeuniversity 1 100
1 leewhoeeuniversity 1 90

And so on, until you complete the calculation of the left input for all rows.

The results are 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 a left JOIN,

?

1 2 3 4 5

SELECT * FROM Test4 a outer apply (select top 2 * to 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

Because there is no ' DePaul ' record in the Test4score table, it is populated with a null value.

There are, of course, more ways to implement this requirement, such as using the Rank function Row_number:

?

1 2 3 4

Select B.name,a.score from (select *,row_number ()-Partition by Test4id ORDER BY score Desc) as rum to Test4score) a INNER JOIN Test4 B on B.id=a.test4id where rum < 3

Results:

Name Score
Leewhoeeuniversity 100
Leewhoeeuniversity 90
Leewhoee 90
Leewhoee 82

This method is implemented using the Row_number () and partition by as described above, for more information, see:

SQL Server rank function rank,dense_rank,ntile,row_number

There is an older method, but you must add an identity column to the TEST4SOCRE table structure as follows:

?

1 2 3 4 5 6

CREATE TABLE Test4score (ID int identity (1,1), Test4id int, score int)

New data:

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 statement with a string query:

?

1 2 3 4

Select A.name,b.score from Test4 a inner join Test4score B to A.id=b.test4id where b.ID in (select top 2 ID from test 4score where test4id=b.test4id ORDER BY score Desc)

Results:

Name Score
Leewhoeeuniversity 100
Leewhoeeuniversity 90
Leewhoee 90
Leewhoee 82