时间戳

首页 > 解决方案 > 按人选择前 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 个分数需要与locationId其他 3个不同

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

eventId locationId score athlete
8739    73          48  matt
8734    73          46  matt
8788    73          45  matt
8789    6           43  matt

我已经尝试写出将使用 a 的查询，GROUP BY HAVING MIN(locationId) != MAX(locationId)但我不确定如何在执行ORDER BYand的同时完成该操作LIMIT。

我也尝试过自加入，但我不确定如何根据s.scoreand返回最佳结果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;

标签： mysqlsqlmysql-8.0

解决方案

您可以使用row_number分析函数和limit子句，包括self-join如下一个

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 演示

推荐阅读