查询每个同学不是最高分的记录

id student_id score
1 1 90
2 1 85
3 1 70
4 2 80
5 2 60

SELECT * FROM 
( SELECT *
  FROM table1 
  WHERE score not in (SELECT MAX(score) score FROM table1 group by student_id)
) t1
WHERE student_id NOT IN 
    (SELECT student_id FROM table1 GROUP BY student_id HAVING COUNT(student_id)<=1)
ORDER BY student_id ASC

先按学生进行分组，查出最高分对应的id。然后再查询分数表，并且id not in（最大分对应id子查询）

select a.*
from
(select student_id,score,row_number()over(partition by student_id order by score desc) as rn from table_name)a
where a.rn<>1

望采纳