sql查询每科成绩的最高分_sql server 取每一个学生多个科目当中分数最高的记录...

同表当中，取每一个学生多个科目当中分数最高的记录(name,sub,scores)

WITH tmp AS(SELECT stuid,sub,scores,ROW_NUMBER() OVER (PARTITION BY stuid ORDER BY scores DESC) as num

FROM score

)SELECT * FROM tmp WHERE num<=1

同表当中，取每一个科目最高的记录(name,sub,scores)：

WITH tmp AS(SELECT stuid,sub,scores,ROW_NUMBER() OVER (PARTITION BY sub ORDER BY scores DESC) as num

FROM score

)SELECT * FROM tmp WHERE num<=1

两表当中，取每一个学生多个科目当中分数最高的记录(id,name,sub,scores)：

SELECT a.id,a.name,b.sub,b.scores FROM students AS a JOIN score AS b ON a.id=b.stuid JOIN score AS c ON b.stuid=c.stuid

GROUP BY a.id,a.name,b.name,b.scores HAVING b.scores=MAX(c.scores)

----------------------------------------------------------------------------

或者

WITH tmp AS(SELECT a.id,a.name,b.sub AS sub,b.scores,ROW_NUMBER() OVER (PARTITION BY b.stuid ORDER BY scores DESC) as num

FROM students AS a JOIN score AS b ON a.id=b.stuid

)SELECT * FROM tmp WHERE num<=1

两表当中，取每一个科目最高的记录(id,name,sub,scores)：

SELECT a.id,a.name,b.sub,b.scores FROM students AS a JOIN score AS b ON a.id=b.stuid JOIN score AS c ON b.sub=c.sub

GROUP BY a.id,a.name,b.sub,b.scores HAVING b.scores=MAX(c.scores)

----------------------------------------------------------------------------

或者

WITH tmp AS(SELECT a.id,a.name,b.sub AS sub,b.scores,ROW_NUMBER() OVER (PARTITION BY b.sub ORDER BY scores DESC) as num

FROM students AS a JOIN score AS b ON a.id=b.stuid

)SELECT * FROM tmp WHERE num<=1

mysql：

两表当中，取每一个科目最高的记录(id,name,sub,scores)：

SELECT d.name,c.stuid,c.scores,c.sub,c.num FROM (SELECT stuid,scores,sub,(SELECT COUNT(*) FROM score AS b WHERE a.stuid=b.stuid AND a.scores<=b.scores) AS num

FROM score AS a GROUP BY stuid,scores,sub) AS c JOIN students AS d ON c.stuid=d.id WHERE c.num<=1

mysql：

两表当中，取每一个学生多个科目当中分数最高的记录(id,name,sub,scores)：

(这条语句的问题是：当同一个科目的最高分有两个或两个以上时，数据就会被过滤掉，不知道怎么解决……)

SELECT d.name,c.stuid,c.scores,c.sub,c.num FROM (SELECT stuid,scores,sub,(SELECT COUNT(*) FROM score AS b WHERE a.sub=b.sub AND a.scores<=b.scores) AS num

FROM score AS a GROUP BY stuid,scores,sub) AS c JOIN students AS d ON c.stuid=d.id WHERE c.num<=1