sql 查询每科的前三名

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 A
WHERE 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