我有这个表格包含学生id和他们的分数
|student id |score| | aac | 3 | | aaa | 6 | | aac | 5 | | aaa | 7 | | aad | 3 |
我想找出每个学生的最高分数。我该怎么做？我试着检查了名单上的每个学生证，但效率不高。
sz81bmfz1#
对于您给出的精确表，简单的分组查询应该可以：
SELECT student_id, MAX(score) AS max_score FROM yourTable GROUP BY student_id;
kqlmhetl2#
你可以使用窗口功能 row_number ```selectstudent_id,scorefrom(select*,row_number() over (partition by student_id order by score desc) as rnfrom yourTable) subqwhere rn = 1
row_number
2条答案
按热度按时间sz81bmfz1#
对于您给出的精确表，简单的分组查询应该可以：
kqlmhetl2#
你可以使用窗口功能
row_number```select
student_id,
score
from
(
select
*,
row_number() over (partition by student_id order by score desc) as rn
from yourTable
) subq
where rn = 1