MySQL 8.0

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

select version();


version()


8.0.30


CREATE TABLE users (
  id int auto_increment,
  uid varchar(255),
  nickname varchar(255),
  classroom int,
  primary key (`id`)
);
insert into users(uid, nickname, classroom) values
('令狐冲', 'linghuchong', 1),
('郭靖', 'guojing', 1),
('小龙女', 'xiaolongnv', 2),
('黄蓉', 'huangrong', 2),
('杨过', 'yangguo', 1),
('欧阳锋', 'ouyangfeng', 1);

Records: 6  Duplicates: 0  Warnings: 0


CREATE TABLE scores (
  id int auto_increment,
  uid varchar(255),
  paperId int,
  score int,
  firstTry int,
  primary key (`id`)
);
insert into scores(uid, paperId, score, firstTry) values
('令狐冲', 25, 90, 0),
('令狐冲', 25, 95, 1),
('郭靖', 25, 80, 0),
('郭靖', 25, 90, 1),
('小龙女', 25, 92, 1),
('小龙女', 25, 88, 0),
('黄蓉', 25, 95, 0),
('黄蓉', 25, 95, 1),
('杨过', 25, 94, 0),
('杨过', 25, 94, 1);

Records: 10  Duplicates: 0  Warnings: 0


SELECT u.*, s.* 
FROM users u
LEFT JOIN LATERAL
(
  select *
  from scores sc
  where sc.paperId = 25 
  AND sc.uid = u.uid 
  ORDER BY sc.score DESC, sc.firstTry DESC
  LIMIT 1
) s ON true
WHERE u.classroom = 1
ORDER BY s.score DESC, s.id ASC;


id
uid
nickname
classroom
id
uid
paperId
score
firstTry


1
令狐冲
linghuchong
1
2
令狐冲
25
95
1

5
杨过
yangguo
1
10
杨过
25
94
1

2
郭靖
guojing
1
4
郭靖
25
90
1

6
欧阳锋
ouyangfeng
1
null
null
null
null
null

id	uid	nickname	classroom	id	uid	paperId	score	firstTry
1	令狐冲	linghuchong	1	2	令狐冲	25	95	1
5	杨过	yangguo	1	10	杨过	25	94	1
2	郭靖	guojing	1	4	郭靖	25	90	1
6	欧阳锋	ouyangfeng	1	null	null	null	null	null