mysql - 按人选择前 4 个分数但至少需要两个位置

我有数据，例如eventId locationId score athlete8739 73 48 matt8734 73 46 matt8788 73 45 matt8738 73 44 matt8787 73 44 matt8735 73 43 matt8789 6 43 matt我需要按人捕获前 4 个分数，但前 4 个分数中至少有 1 个需要来自与其他 3 个不同的 locationId在这种情况下，我希望返回eventId locationId score athlete8739 73 48 matt8734 73 46 matt8788 73 45 matt8789 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 score2FROM singles s INNER JOIN singles s2 ON s.athlete = s2.athlete AND s.locationid != s2.locationidWHERE s.athlete = 'matt'ORDER BY score DESC; 最佳答案 因此，您真正想要的是前三个分数，然后是保证至少有两个位置的第一个分数。这是一个相当困难的条件，但我认为这可以解决问题:with s as ( select t.*, row_number() over (partition by athlete order by score desc) as seqnum from t ), s3 as ( select s.* from s where seqnum <= 3 )select *from s3union all(select s.* from s where ( (select count(distinct locationid) from s3) > 1 and seqnum = 4 ) or ( (select count(distinct locationid) from s3) = 1 and seqnum = (select min(seqnum) from s where locationid not in (select locationid from s3) ) )); Here 是一个 dbfiddle。关于mysql - 按人选择前 4 个分数但至少需要两个位置，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/53899652/