mysql - 如何获得所有最大列值的总和

Question

我有一个问题，请在下方获取分类和点数。

这是表查询

-------------------------------------------
id    title    cate    query_id    point   |
-------------------------------------------
1     A        1       1           1       |
2     B        1       1           3       |
3     C        1       1           2       |
4     A        2       2           3       |
5     B        2       2           2       |
6     C        1       2           1       |
--------------------------------------------

如果我 groupby(cate, query) 并获得最高分

----------------------------
cate    query_id    point   |
----------------------------
1       1           3       |
2       2           3       |
1       2           1       |
----------------------------

但我想像这样通过 cate 获得最大点的总和:

--------------
cate    point |
--------------
1         4   | 
2         3   |
--------------

我希望你能帮助我。

Answer 1

您可以像这样使用派生表子查询对聚合进行聚合:

SELECT cate, SUM(point) AS point
FROM (
    SELECT cate, query_id, MAX(point) AS point
    FROM MyTable
    GROUP BY cate, query_id
) AS t
GROUP BY cate;