废话不多说,直接上脚本
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)
查询语句:内层中 WHERE B.kemu = A.kemu 其实相当于 拿外层的 name 分组 group by
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
运行结果: