以前的文章讨论过取最新一条数据的sql语句,现在需要按分组取每个分组第一条数据。这样的需求也是经常会遇到,比如全年级4个班期末考试成绩,如何一次性取出每个班的第一名的成绩?
MS SQLServer 中有几种方式可以实现:
建成绩表:
create table tb_score(grade varchar(12),name varchar(30),score decimal(10,2))
导入学生成绩:
-- ----------------------------
-- Records of [tb_score]
-- ----------------------------
INSERT INTO [dbo].[tb_score] VALUES (N'2301', N'张珊', N'89.00')
GO
INSERT INTO [dbo].[tb_score] VALUES (N'2301', N'李思', N'77.00')
GO
INSERT INTO [dbo].[tb_score] VALUES (N'2301', N'王尔', N'58.00')
GO
INSERT INTO [dbo].[tb_score] VALUES (N'2302', N'赵仪', N'92.00')
GO
INSERT INTO [dbo].[tb_score] VALUES (N'2302', N'周琦', N'39.00')
GO
INSERT INTO [dbo].[tb_score] VALUES (N'2302', N'郑武', N'63.00')
GO
INSERT INTO [dbo].[tb_score] VALUES (N'2303', N'陈振', N'77.00')
GO
INSERT INTO [dbo].[tb_score] VALUES (N'2303', N'许有', N'81.00')
GO
INSERT INTO [dbo].[tb_score] VALUES (N'2303', N'庞通', N'87.00')
GO
INSERT INTO [dbo].[tb_score] VALUES (N'2304', N'闫福', N'97.00')
GO
INSERT INTO [dbo].[tb_score] VALUES (N'2304', N'庄改', N'58.00')
GO
INSERT INTO [dbo].[tb_score] VALUES (N'2304', N'白歌', N'91.00')
GO
第一种实现方法:使用ROW_NUMBER函数
select * from
(
select ROW_NUMBER() over(PARTITION By grade ORDER BY score DESC) as rownum, grade,name,score
from score_t
) T
where T.rownum = 1
第二种实现方法:
select a.* from tb_score a,
(
select grade ,max(score) as score
from tb_score
group by grade
) b
where a.grade = b.grade and a.score = b.score
order by a.grade
第三种方法:
SELECT grade,name,score FROM tb_score a
WHERE NOT EXISTS
(SELECT 1 FROM tb_score b
WHERE a.grade=b.grade AND a.score<b.score )
order by grade
以上这三种方法做个抛砖引玉,大家也可以继续探索其它更简洁的实现方法。如果有并列第一名,大家可以试验一下,看哪种语句可以取出来并列第一。