MySQL 选择每个运动员的最佳(和最老)表现、类别

Question

我正在尝试从下表构建 SQL 查询(示例):

Example of table with name "performances"

这是带有运动表现的表格。我想从这个表中选择每个学科和一组一个或多个类别的最佳表现。尽管每个运动员的最佳表现值是成绩表中的两倍或更多，但他只能获得一次成绩。

Here is expected result from table "performances"

实际上我有这个 SQL 查询，但是从子查询中连接所有具有运动员 ID 和最佳值最佳值的行:

SELECT 

p.athlete_id, p.value 

FROM

(SELECT athlete_id, MAX(value) AS best FROM performances 

WHERE discipline_id = 32 AND category_id IN (1,3,5,7,9) 

GROUP BY athlete_id) f

INNER JOIN performances p 

ON p.athlete_id = f.athlete_id AND p.conversion = f.best

ORDER BY p.value DESC, p.created 

请问，如何为每个运动员只加入一行，该运动员具有最旧创建的属性？

Answer 1

要根据最大值value获取每个discipline的每个athlete的单行，您可以执行自左连接，以处理平局情况或者如果单个运动员有超过1行具有相同的最大值，您可以使用case语句来选择日期最旧的行

select a.*
from performances a
left join performances b
on a.discipline_id = b.discipline_id
and a.athlete_id = b.athlete_id
and case when a.value = b.value
    then a.created > b.created
    else a.value < b.value
    end 
where b.discipline_id is null

DEMO

此外，您可以在 where 子句中添加过滤器

and a.discipline_id = 32 
and a.category_id IN (1,3,5,7,9)

DEMO