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

Question

我有这样的数据

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 个需要来自不同的 locationId比其他3个

在这种情况下，我希望返回

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;

Answer 1

您可以使用row_number 分析函数和limit 子句，包括如下所示的自连接/p>

select locationId, score, athlete
 from
   (
    select locationId, score, athlete, rn, rn2
      from(
            select *
              from
              (
                 with singles(locationId, score, athlete) as
                 (
                  select 73, 48, 'matt' union all
                  select 73, 46, 'matt' union all
                  select 73, 45, 'matt' union all
                  select 73, 44, 'matt' union all
                  select 73, 44, 'matt' union all
                  select 73, 43, 'matt' union all
                  select  6, 43, 'matt'     
                 )
                 select row_number() over (partition by s.locationId order by s.score desc) as rn,
                        row_number() over (partition by s2.locationId order by s2.score desc) as rn2,
                        s.athlete, s.locationId, s.score
                   from singles s join singles s2 
                     on s.score = s2.score
                  where s.athlete = 'matt'
               ) q1
               order by score desc, rn, rn2 
            ) q2 
      group by locationId, score
     having sum(rn) <= sum(rn2)
      order by rn, score desc
      limit 4
   ) q3
 order by score desc

dbfiddle.uk demo