SQL Server 8: SQL queries the two courses with the highest score per student

This is an interview question. If you have time to write it down today, you may encounter such problems as a reference! When I first saw this question, many people may think of keywords.TopIn fact, the keyword used here isPartition,

Now, let's take a look at the table structure and data!

 

 

Next, let's take a look at the Partition Function and execute the statement.

 

 
Select C. *, row_number () over (partition by C. ID order by C. sorce DESC) rank from DBO. courcer C
 

The result is as follows:

 

The final result is clearly displayed here!

 

View code

 Select S. * , C. *  From  DBO. Student s  Left   Join  (  Select C. * , Row_number () Over (Partition By C. ID Order   By C. sorce Desc ) Rank From  DBO. courcer c) c  On S. ID =  C. ID  Where C. Rank <=  2