废话不多说,直接上脚本
- 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
运行结果:
来源: http://www.bubuko.com/infodetail-1983415.html