本文介绍了一种使用SQL查询语句来对学生按照不同年级进行成绩排名的方法，并特别关注于选取每个年级中成绩最高的前两名学生。
按学生年级分组，取各年级前两名的学生信息
SELECT *
FROM (SELECT ST.ID, ST.GRADE, ST.NAME, ST.SCORE
FROM STUDENTS ST
JOIN (SELECT MAX(SCORE) AV, GRADE FROM STUDENTS GROUP BY GRADE) T ON ST.GRADE =
T.GRADE
WHERE ST.SCORE = T.AV
UNION ALL
SELECT ST.ID, ST.GRADE, ST.NAME, ST.SCORE
FROM STUDENTS ST
JOIN (SELECT MAX(SCORE) AV, GRADE
FROM (SELECT ST.ID, ST.GRADE, ST.NAME, ST.SCORE, T.AV
FROM STUDENTS ST
JOIN (SELECT MAX(SCORE) AV, GRADE
FROM STUDENTS
GROUP BY GRADE) T ON ST.GRADE = T.GRADE
WHERE ST.SCORE != T.AV)
GROUP BY GRADE) T ON ST.GRADE = T.GRADE
WHERE ST.SCORE = T.AV)
ORDER BY GRADE
SELECT *
FROM (SELECT ST.ID, ST.GRADE, ST.NAME, ST.SCORE
FROM STUDENTS ST
JOIN (SELECT MAX(SCORE) AV, GRADE FROM STUDENTS GROUP BY GRADE) T ON ST.GRADE =
T.GRADE
WHERE ST.SCORE = T.AV
UNION ALL
SELECT ST.ID, ST.GRADE, ST.NAME, ST.SCORE
FROM STUDENTS ST
JOIN (SELECT MAX(SCORE) AV, GRADE
FROM (SELECT ST.ID, ST.GRADE, ST.NAME, ST.SCORE, T.AV
FROM STUDENTS ST
JOIN (SELECT MAX(SCORE) AV, GRADE
FROM STUDENTS
GROUP BY GRADE) T ON ST.GRADE = T.GRADE
WHERE ST.SCORE != T.AV)
GROUP BY GRADE) T ON ST.GRADE = T.GRADE
WHERE ST.SCORE = T.AV)
ORDER BY GRADE
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
