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