mysql - 根据枚举值和按日期分组获取多列中的最高分数和名称

Question

我们的扇区列类型枚举值为 ['Midfielder', 'Forward', 'Defender', 'Goalkeeper']。

我们希望按日期输出每个部门的最高分数和每个部门组的玩家姓名。

如果没有在任何日期预设数据，那么我们希望为 null 或 0。

date       │ mid_score │ midfielder │ def_score │ defender
2017-12-26 │ 91        │ Dele Alli  │ 74         │ Jan Vertonghen
2017-12-27 │ 61        │ Eric Dier  │ 68         │ maya yoshida

Answer 1

您想要的聚合查询将按日期和扇区进行分组，然后查找该组的最高分数。我们可以将此查询放入子查询中，然后将您的表连接到其中，条件是日期、扇区和分数与子查询中的日期、扇区和最大分数相匹配。

SELECT t1.*
FROM yourTable t1
INNER JOIN
(
    SELECT date, sector, MAX(score) AS max_score
    FROM yourTable
    GROUP BY date, sector
) t2
    ON t1.date = t2.date AND
       t1.sector = t2.sector AND
       t1.score = t2.max_score
ORDER BY
    t1.date,
    t1.sector;

对于丢失的数据，处理该问题的一种选择是使用日历表。我认为这个答案有点太大了，无法容纳，但我会把它作为家庭作业留给你。

编辑:如果您希望每个日期都有一行，那么我们可以按日期对外部查询进行第二次汇总:

SELECT
    t1.date,
    GROUP_CONCAT(t1.sector ORDER BY t1.score) sectors,
    GROUP_CONCAT(t1.score ORDER BY t1.score) scores
FROM yourTable t1
INNER JOIN
(
    SELECT date, sector, MAX(score) AS max_score
    FROM yourTable
    GROUP BY date, sector
) t2
    ON t1.date = t2.date AND
       t1.sector = t2.sector AND
       t1.score = t2.max_score
GROUP BY
    t1.date
ORDER BY
    t1.date;