求每个人相同试卷成绩最好的那条记录,如果最高成绩有相同的,获取最近的那条记录

考试成绩表,exam

|  id |  papge_id | user_id  |  score | created_at  |
| -主键- | -试卷id- | -用户id- | -成绩- | --考试日期-- |
| 1  | 1  | 4  | 80  | 2022-09-01  |
| 2  | 1  | 4  | 90  | 2022-09-02  |
| 3  | 1  | 4  | 90  | 2022-09-03 |
| 1  | 1  | 4  | 80  | 2022-09-04  |
| 4  | 1  | 5  | 70  | 2022-09-01  |
| 5  | 1  | 5  |  50 | 2022-09-02  |
| 6  | 1  | 5  | 55  | 2022-09-03  |
| 7  | 1  | 6  | 55  |  2022-09-01 |
| 8  | 1  | 6  | 60  | 2022-09-02  |
| 9  | 1  | 6  |  40 | 2022-09-03  |

sql:

select * from exam inner join (select user_id, max(score) as max from exam group by user_id) as high on high.user_id =exam.user_id
where exam.score=high.max and exam.paper_id =1

结果:

| 2  | 1  | 4  | 90  | 2022-09-02  |
| 3  | 1  | 4  | 90  | 2022-09-03 |
| 4  | 1  | 5  | 70  | 2022-09-01  |
| 8  | 1  | 6  | 60  | 2022-09-02  |

因为user_id=4时,最高分90有两条,所以返回时会出现两条记录,有什么办法可以获取id=3时的那条记录,
如何得到下面这样的结果,希望sql语句尽量的简短,用laravel上的方法更好

| 3  | 1  | 4  | 90  | 2022-09-03 |
| 4  | 1  | 5  | 70  | 2022-09-01  |
| 8  | 1  | 6  | 60  | 2022-09-02  |
最佳答案

MySQL:

SELECT *
  FROM (
      SELECT rank() OVER(PARTITION BY papge_id, user_id ORDER BY score DESC, created_at DESC) AS r, exam.*
        FROM exam
    ) t
 WHERE t.r = 1

结果(r没用):

r id papge_id user_id score created_at
1 3 1 4 90 2022-09-03
1 4 1 5 70 2022-09-01
1 8 1 6 60 2022-09-02
3周前 评论
wenreal (楼主) 3周前
讨论数量: 4

找到一个方法,但筛选条件加在不同位置的结果是不同的,示例2

1.求每个人的最高成绩(不区分试卷),成绩相同取日期最近的那条
select * from (select * from exam order by score desc,created_at desc limit 10000000 ) as e group by e.user_id

file

2.paper_id(试卷id)=1,每个人的最高成绩,成绩相同取日期最近的那条
2.1 
select * from (select * from exam order by score desc,created_at desc limit 10000000 ) as e 
where e.paper_id=1 group by e.user_id
结果不对

file

2.2
select * from (select * from exam where paper_id=1 order by score desc,created_at desc limit 1000000000 ) as e GROUP by user_id
结果是对的

file

参考:www.cnblogs.com/huanyue9987/p/1584...

3周前 评论
wenreal (作者) (楼主) 3周前

MySQL:

SELECT *
  FROM (
      SELECT rank() OVER(PARTITION BY papge_id, user_id ORDER BY score DESC, created_at DESC) AS r, exam.*
        FROM exam
    ) t
 WHERE t.r = 1

结果(r没用):

r id papge_id user_id score created_at
1 3 1 4 90 2022-09-03
1 4 1 5 70 2022-09-01
1 8 1 6 60 2022-09-02
3周前 评论
wenreal (楼主) 3周前

讨论应以学习和精进为目的。请勿发布不友善或者负能量的内容,与人为善,比聪明更重要!