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 |