MYSQL with table join on query（Could the query works more efficient?）

Question

I've two tables, the users:

and the scores of exercise of the users, paperId shows the exercise ID, and the firstTry shows if it is the first try (value 0) or not (others value):

For a given classroom (e.g. classrootm=1), I want to query the max exercise score of everyone with the classroom=1, even if the user did not finish the exercise (like the man called 欧阳锋).

My query sequence is:

select u.*, s.* from users u
left join
(
    select s.*
    from (                
        select uid, max(score) as score, max(firstTry) as times
        from scores
        where paperId = 25 
        group by uid
    ) t, scores s
    where
    s.paperId = 25
    AND s.uid = t.uid AND s.score = t.score AND s.firstTry = t.times
) s
on s.uid = u.uid
where classroom = 1
ORDER BY s.score DESC, s.id ASC

Could it be more simple? or how to write the mysql sequence to make the query works more efficient? It seems my query joins table three times

The code and demo go here: http://sqlfiddle.com/#!9/a415566/8

Answer 1

You want to outer join the best score row per classroom-1 user. The best method to do this is probably a lateral join:

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;

Demo: https://dbfiddle.uk/1LwG92h4

Docs on lateral joins in MySQL: https://dev.mysql.com/doc/refman/8.0/en/lateral-derived-tables.html