Sqlserver: Class ranking (forwarding)

ID, name (person name), Class (class), Fen (score)
1 JJ 1 88
2 J1 1 90
3 J2 2 70
Select the first two scores for each class and display the class, name, score ????????

Create Table # T (ID int, name varchar (20), Class varchar (10), Fen INT)
Insert into # T
Select 1, 'JJ ', '1', 88 Union all
Select 2, 'j1', '1', 90 Union all
Select 3, 'j2 ', '1', 70 Union all
Select 4, 'j3', '2', 89 Union all
Select 5, 'ja ', '3', 96 Union all
Select 6, 'jb ', '3', 63

Solution 1:
Select *
From # t
Where (select count (*) from # t where class = "T". Class and Fen> T. FEN) = 0 -- first place
Where (select count (*) from # t where class = "T". Class and Fen> T. FEN) = 1 -- second name
Where (select count (*) from # t where class = "T". Class and Fen> T. FEN) <2 -- first 2
Where (select count (*) from # t where class = "T". Class and Fen <t. FEN) = 0 -- last one
Where (select count (*) from # t where class = "T". Class and Fen <t. FEN) = 1 -- Second to last
Where (select count (*) from # t where class = "T". Class and Fen <t. FEN) <2 -- last two

Solution 2:
Select T. * from # t where Fen in (select Top 2 fen from # t where class = T. Class) order by class, fen

Solution 3:
Select *
From
(
Select row_number () over (partition by class order by Fen DESC) as rank, * from # T
) T
Where rank <= 2
Order by class, rank, fen
Note: Ranking Functions
Rank (): sparse ranking, which may be tied together, leading to continuous ranking of numbers. If two of them are tied for the first place, the next ranking is the third!
Dense_rank (): Non-sparse ranking. The ranking may be parallel, but the ranking numbers will not be interrupted. If two of them are tied for the first place, the next ranking is the second!
Ntile (group count): (drawer distribution) distributes data by group. It mainly depends on whether the total number of rows can be evenly distributed by the number of groups. If the total number of rows cannot be evenly distributed, the data is limited to the previous group!
Row_number (): allocate the ID of a unique logical row record, which is usually used for paging
The over clause also supports aggregate functions and window partition clauses.

Solution 4:
Select distinct T .*
From # T
Cross apply (select Top 2 * from # t B where a. Class = B. class order by Fen DESC) as t -- Internal (left value) multi-value cross table
Order by T. Class, T. Fen DESC

Solution 5:
Select distinct T .*
From # T
Outer apply (select Top 2 * from # t B where a. Class = B. class order by Fen DESC) as t -- External (Right Value) multi-value cross table
Order by T. Class, T. Fen DESC