从单个表中生成多个前 1 的 SQL 查询

Question

我想生成一个查询来提取一年中不同距离的人的最佳结果行，同时所有结果都列在一个表中。我如何制定一个查询来做到这一点？
所以表格看起来像这样:

Date    distance    Name    time  
2020    10          Bert    00:19:38  
2020    10          Bert    00:20:02  
2019    10          Bert    00:19:52  
2020    50          Bert    01:50:00  
2020    50          Bert    01:45:34  
2019    50          Bert    01:44:00  
2020    100         Bert    04:00:00  
2020    100         Bert    03:50:00  
2019    100         Bert    04:02:00

伯特在 2020 年的结果线需要看起来像这样......

year    Name    best10     best50     best100
2020    Bert    00:19:38   01:45:34   03:50:00

Answer 1

嗯。 . .使用条件聚合

select year, name,
       max(case when distance = 10 and seqnum = 1 then time end) as best_10,
       max(case when distance = 50 and seqnum = 1 then time end) as best_50,
       max(case when distance = 100 and seqnum = 1 then time end) as best_100
from (select t.*,
             row_number() over (partition by year, name, distance order by time) as seqnum
      from t
     ) t
group by year, name;