WITH tt AS( SELECT 100 ID,'语文' course,80 score FROM dual UNION ALL SELECT 100,'数学',85 FROM dual UNION ALL SELECT 100,'英语',90 FROM dual UNION ALL SELECT 101,'语文',60 FROM dual UNION ALL SELECT 101,'数学',70 FROM dual UNION ALL SELECT 101,'英语',100 FROM dual UNION ALL SELECT 102,'语文',100 FROM dual UNION ALL SELECT 102,'数学',90 FROM dual UNION ALL SELECT 102,'英语',90 FROM dual ) SELECT ID,COURSE, SCORE FROM (SELECT ID, COURSE, SCORE, RANK() OVER(PARTITION BY ID ORDER BY SCORE DESC) MM FROM TT) WHERE MM <= 2