SQL query top three per section

Nonsense not much to say directly on the script

CREATE TABLE [dbo].[Students](
    [ID] [int] IDENTITY(1,1) not NULL,
    [name] [nchar]( -)NULL,
    [Kemu] [nchar]( -)NULL,
    [score] [int]  not NULL,
 CONSTRAINT [pk_students] PRIMARY KEY CLUSTERED 
(
    [ID] ASC
) with(Pad_index= OFF, Statistics_norecompute= OFF, Ignore_dup_key= OFF, Allow_row_locks=  on, Allow_page_locks=  on) on [PRIMARY]
)  on [PRIMARY]

GO

Insert  intoStudentsValues('Zhang San','language', the)
Insert  intoStudentsValues('John Doe','language', the)
Insert  intoStudentsValues('Harry','language', the)
Insert  intoStudentsValues('Zhao Liu','language', the)
Insert  intoStudentsValues('Weather','language', -)
Insert  intoStudentsValues('Bastard','language', the)
Insert  intoStudentsValues('unitary nine','language', the)
Insert  intoStudentsValues('Big 10','language', the)



Insert  intoStudentsValues('Zhang San','Mathematics', -)
Insert  intoStudentsValues('John Doe','Mathematics', the)
Insert  intoStudentsValues('Harry','Mathematics', the)
Insert  intoStudentsValues('Zhao Liu','Mathematics', the)
Insert  intoStudentsValues('Weather','Mathematics', the)
Insert  intoStudentsValues('Bastard','Mathematics', the)
Insert  intoStudentsValues('unitary nine','Mathematics', the)
Insert  intoStudentsValues('Big 10','Mathematics', $)


Insert  intoStudentsValues('Zhang San','English', -)
Insert  intoStudentsValues('John Doe','English', the)
Insert  intoStudentsValues('Harry','English', the)
Insert  intoStudentsValues('Zhao Liu','English', the)
Insert  intoStudentsValues('Weather','English', -)
Insert  intoStudentsValues('Bastard','English', +)
Insert  intoStudentsValues('unitary nine','English', the)
Insert  intoStudentsValues('Big 10','English', in)

Query statement: the WHERE B.kemu = A.kemu in the inner layer is actually equivalent to taking the outer name grouping group by

SELECT * 
 fromStudents AWHERENameinch(SELECT TOP 3name fromStudents BWHEREB.kemu=A.kemuORDER  byB.scoreDESC)
ORDER  byA.kemu, A.scoreDESC

Operation Result:

SQL query top three per section