CREATE TABLE TB (学号 INT,第几次考试 INT, 考试成绩 INT)
INSERT INTO TB SELECT 1,1,70
UNION ALL SELECT 2,1,65
UNION ALL SELECT 1,2,80
UNION ALL SELECT 3,4,50
UNION ALL SELECT 2,3,88
UNION ALL SELECT 3,1,90
union all select 1,1,80
union all select 1,3,80
union all select 2,2,65
union all select 3,3,90
select * from TB
select 学号,max(考试成绩)最高分 from TB group by 学号
select TB.学号,第几次考试,BB.最高分 from TB,(select 学号,max(考试成绩)最高分 from TB group by 学号)BB
where 考试成绩=BB.最高分 and TB.学号=BB.学号
结果如下:
--测试环境
CREATE TABLE TB (STID INT,SORTID INT, SCORE INT)
INSERT INTO TB SELECT 1,1,70
UNION ALL SELECT 2,1,65
UNION ALL SELECT 1,2,80
UNION ALL SELECT 3,4,50
UNION ALL SELECT 2,3,88
UNION ALL SELECT 3,1,90
--查询
WITH TBCTE(STID,SORTID,SCORE)
AS
(
SELECT STID,SORTID,SCORE FROM TB A WHERE NOT EXISTS
(SELECT 1 FROM TB WHERE STID=A.STID AND SCORE>A.SCORE)
)
SELECT TA.Name,TB.SCORE,TB.SORTID
FROM TA INNER JOIN TBCTE TB
ON TA.STID=TB.STID
--结果
/*
Name SCORE SORTID
---------- ----------- -----------
小明 80 2
小红 88 3
小强 90 1
select B.name,A.sortid,A.score from TABLEScore as A join
(select Stid,max(score)as maxscore from TABLEScore group by stid)as AA
on A.stid = AA.stid and A.score = AA.maxscore
join tablestudent B on A.stid = B.stid