具有 GROUP BY、SUM、MAX 和子查询的 MySQL 查询

Question

我有以下表格结构:

Id  Num1  Num2  Type  Num3
1    2     2     1     4
1    3     1     2     5
1    1     1     3     2
2    2     1     1     3
2    0     1     2     2
2    4     3     3     6

我需要一个按“Id”、“Num1”的总和、“Num2”的总和、“Num3”的最大值以及与“Num3”的最大值相关的“类型”进行分组的查询。因此，所需的输出是:

Id  Sum(Num1)  Sum(Num2)  type  Max(Num3)
1       6          4       2       5   
2       6          4       3       6

没有这个相关的“类型”，下面的查询工作正常:

SELECT
 Id,
 SUM(Num1),
 SUM(Num2),
 MAX(Num3)
GROUP BY
 Id

我尝试了不同的子查询方法，但仍然无法正常工作。

Answer 1

你的问题是关于每组最大值(value)问题的一些旋转。在这种情况下，我们可以使用子查询来查找每个 Id 的最大 Num3 值。但是，在同一个子查询中，我们还计算总和聚合。

SELECT
    t1.Id,
    t2.s1,
    t2.s2,
    t1.Type,
    t1.Num3
FROM yourTable t1
INNER JOIN
(
    SELECT Id, SUM(Num1) AS s1, SUM(Num2) AS s2, MAX(Num3) AS m3
    FROM yourTable
    GROUP BY Id
) t2
    ON t1.Id = t2.Id AND t1.Num3 = t2.m3;

作为 MySQL 8+ 的小贴士，为了避邪，我们还可以使用分析函数编写查询:

SELECT Id, s1, s2, Type, Num3
FROM
(
    SELECT
        Id,
        SUM(Num1) OVER (PARTITION BY Id) s1,
        SUM(Num2) OVER (PARTITION BY Id) s2,
        Type,
        Num3,
        MAX(Num3) OVER (PARTITION BY Id) m3
    FROM yourTable
) t
WHERE Num3 = m3;