Row_number () over (PARTITION by xxx order by xxx) group sorting

--//Create an information tableCREATE TABLEUser_student (IDdecimal( -,0)Identity(1,1), St_namenvarchar( -), classnvarchar(Ten), scoredecimal( -,2))--//Insert test data ============start===================Insert  intouser_student (St_name,class,score)Values('Zhang San','Armor',' -')  Insert  intouser_student (St_name,class,score)Values('Zhang Si','Armor',' $') Insert  intouser_student (St_name,class,score)Values('Zhang Five','Armor',' the') Insert  intouser_student (St_name,class,score)Values('lie triple','b',' the') Insert  intouser_student (St_name,class,score)Values('John Doe','b',' the') Insert  intouser_student (St_name,class,score)Values('Lee','b',' +') Insert  intouser_student (St_name,class,score)Values('Wang San','C',' the') Insert  intouser_student (St_name,class,score)Values('Wangsi','C',' the') Insert  intouser_student (St_name,class,score)Values('Harry','C',' -')--//Insert test data ============end===================Select *  fromuser_student--//Each class scores the first two student informationSELECTSt_name,class,score from (SELECTRow_number () Over(PARTITION byCLASSORDER  byScoreDESC) asNum* fromuser_student) asTWHERENum<=2

In-table data: Output results:

Row_number () over (PARTITION by xxx order by xxx) group sorting