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