mysql 选择前4个_mysql-按人选前4个成绩,但至少需要两个位置

我有诸如

eventId locationId score athlete

8739 73 48 matt

8734 73 46 matt

8788 73 45 matt

8738 73 44 matt

8787 73 44 matt

8735 73 43 matt

8789 6 43 matt

我需要按人捕获前4个得分,但是前4个得分中至少有1个来自与其他3个不同的locationId

在这种情况下,我希望这个返回

eventId locationId score athlete

8739 73 48 matt

8734 73 46 matt

8788 73 45 matt

8789 6 43 matt

我试过写出将使用GROUP BY HAVING MIN(locationId)！= MAX(locationId)的查询,但是我不确定在完成ORDER BY和LIMIT的同时如何实现.

我也尝试过进行自我联接,但是我不确定如何根据s.score和score2返回最佳结果.

似乎步入正轨的自我联接的开始

SELECT s.eventid, s.locationid, athlete, score

, s2.eventid, s2.locationid, s2.athlete, score score2

FROM singles s

INNER JOIN singles s2 ON s.athlete = s2.athlete AND s.locationid != s2.locationid

WHERE s.athlete = 'matt'

ORDER BY score DESC;