首页 > 开发 > SQLServer > 正文

sql server 取每一个学生多个科目当中分数最高的记录

2016-07-19 08:46:41  来源:慕课网
  同表当中,取每一个学生多个科目当中分数最高的记录(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