CREATE TABLE [dbo].[Students](    [ID] [int] IDENTITY(1,1) NOT NULL,    [name] [nchar](20) NULL,    [kemu] [nchar](20) 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 into Students values(‘张三‘,‘语文‘,66)insert into Students values(‘李四‘,‘语文‘,67)insert into Students values(‘王五‘,‘语文‘,68)insert into Students values(‘赵六‘,‘语文‘,69)insert into Students values(‘天气‘,‘语文‘,70)insert into Students values(‘王八‘,‘语文‘,72)insert into Students values(‘幺九‘,‘语文‘,75)insert into Students values(‘大十‘,‘语文‘,80)insert into Students values(‘张三‘,‘数学‘,85)insert into Students values(‘李四‘,‘数学‘,80)insert into Students values(‘王五‘,‘数学‘,75)insert into Students values(‘赵六‘,‘数学‘,69)insert into Students values(‘天气‘,‘数学‘,68)insert into Students values(‘王八‘,‘数学‘,67)insert into Students values(‘幺九‘,‘数学‘,66)insert into Students values(‘大十‘,‘数学‘,65)insert into Students values(‘张三‘,‘英语‘,60)insert into Students values(‘李四‘,‘英语‘,72)insert into Students values(‘王五‘,‘英语‘,76)insert into Students values(‘赵六‘,‘英语‘,77)insert into Students values(‘天气‘,‘英语‘,85)insert into Students values(‘王八‘,‘英语‘,78)insert into Students values(‘幺九‘,‘英语‘,75)insert into Students values(‘大十‘,‘英语‘,71)

SELECT * FROM Students AWHERE name IN (SELECT TOP 3 name                       FROM Students B                      WHERE B.kemu = A.kemu                      ORDER BY B.score DESC)ORDER BY A.kemu, A.score DESC