-- 19、按各科成绩进行排序，并显示排名(实现不完全)

最新推荐文章于 2022-08-25 17:50:14 发布

原创最新推荐文章于 2022-08-25 17:50:14 发布 · 2.2w 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#sql

SQL练习专栏收录该内容

50 篇文章

订阅专栏

此博客汇总了来自三个不同类别（c_id: 01, 02, 03）的前100名学生得分，通过排序和UNION ALL操作展示了每个类别中成绩最高的学生。最后，所有数据统一按照c_id和s_score进行全局降序排列。

SELECT * FROM (SELECT * FROM score WHERE c_id = '01' ORDER BY s_score DESC LIMIT 0,100 ) a
UNION ALL 
SELECT * FROM (SELECT * FROM score WHERE c_id = '02' ORDER BY s_score DESC LIMIT 0,100) b
UNION ALL 
SELECT * FROM (SELECT * FROM score WHERE c_id = '03' ORDER BY s_score DESC LIMIT 0,100) c;

SELECT * FROM score ORDER BY c_id,s_score DESC