手记

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
2人推荐
随时随地看视频
慕课网APP