同表当中,取每一个学生多个科目当中分数最高的记录(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
两表当中,取每