mysqli SUM 和 MAX 结果

Question

大家好，我无法理解如何从数据库中获取 MAX() 和 SUM()

我的数据库有 3 列 id - 东西 - 分数看起来像这样

id-stuf-分数

1-1-2

1-1-3

1-2-1

1-2-3

1-3-1

1-3-3

我需要做一个

MAX(score) 其中 stuff=1 且 id = 1

MAX(score) 其中 stuff=2 且 id = 1

MAX(score) 其中 stuff=3 且 id = 1.

.

等等

然后是SUM(score) 所有的 MAX(score)

我有数百行，我想不出一种方法来实现它

Answer 1

为您需要最大值的每个事物创建子查询表导数，然后将它们全部加在一起。

SELECT SUM(a.max+b.max+c.max) as total
FROM (
    (
        SELECT MAX(score) as max
        FROM tablename
        WHERE stuff = 1 AND id = 1
    ) as a,
    (
        SELECT MAX(score) as max
        FROM tablename
        WHERE stuff = 2 AND id = 1
    ) as b,
    (
        SELECT MAX(score) as max
        FROM tablename
        WHERE stuff = 3 AND id = 1
    ) as c
);

输出:

_____________
|   total   |
+-----------+
|   12345   |
+-----------+