来自 SUM 的 MySQL MAX
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/18441115/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
MySQL MAX from SUM
提问by Wolfchamane
This is freaking me out! Got the following data:
这吓死我了！得到以下数据：
+----+-----+-------+------------+
| ID | REG | VALUE | DATE |
+----+-----+-------+------------+
| 1 | 1A | 100 | 2009-01-01 |
| 1 | 1A | 100 | 2009-02-01 |
| 1 | 1A | 100 | 2009-03-01 |
| 2 | 1B | 100 | 2009-01-01 |
| 2 | 1B | 100 | 2009-02-01 |
| 2 | 1B | 100 | 2009-03-01 |
| 2 | 1C | 100 | 2009-01-01 |
| 2 | 1C | 100 | 2009-02-01 |
| 2 | 1C | 200 | 2009-03-01 |
+----+-----+-------+------------+
PS {edit 0001}:: there's an extra field, which also must be used for filter data, call it {TYPE}, an could get 'SINGLE' or 'MULTIPLE' as value.
PS {edit 0001}:: 有一个额外的字段，它也必须用于过滤数据，称之为{TYPE}，可以获得“SINGLE”或“MULTIPLE”作为值。
I want to get the MAX between SUM(of each different {REG}) for every {ID}. Obviously, this is a simple representation, table got up to 64985928 registers and {DATE} is the filtering data.
我想为每个 {ID} 获得 SUM（每个不同的 {REG}）之间的 MAX。显然，这是一个简单的表示，表最多有 64985928 个寄存器，{DATE} 是过滤数据。
That will be, 1st step get the SUM for each {REG}:
这将是，第一步获取每个 {REG} 的 SUM：
+----+------+
| ID | SUM |
+----+------+
| 1 | 300 |
| 2 | 300 |
| 2 | 400 |
+----+------+
That's:
那是：
SELECT
SUM(value)
FROM
table
WHERE
(date BETWEEN '2009-01-01' AND '2009-03-01')
GROUP BY
reg;
And then, get the MAX from each SUM, which is where I'm stucked:
然后，从每个 SUM 中获取 MAX，这就是我遇到的问题：
+----+------+
| ID | MAX |
+----+------+
| 1 | 300 |
| 2 | 400 |
+----+------+
I've tried:
我试过了：
SELECT
a.id,
MAX(b.sum)
FROM
table a,
(SELECT
SUM(b.value)
FROM
table b
WHERE
(b.date BETWEEN '2009-01-01' AND '2009-03-01') AND (a.id = b.id)
GROUP BY
b.reg);
Any idea? PS: Sorry for mistakes.
任何的想法？PS：有错误请见谅。
PS {edit 0002}Gonna copy original queries and data, so may it helps better.
PS {edit 0002}要复制原始查询和数据，所以可能会更好。
$QUERY:
$查询：
SELECT
clienteid AS "CLIENTE",
SUM(saldo) AS "SUMA"
FROM
etl.creditos
WHERE
(titularidad_tipo LIKE 'TITULAR')
AND
(mes_datos BETWEEN '2008-11-01' AND '2009-10-01')
GROUP BY
nuc
ORDER BY
clienteid;
Got:
得到了：
+---------+-------------+
| CLIENTE | SUMA |
+---------+-------------+
| 64 | 1380690.74 |
| 187 | 1828468.71 |
| 187 | 2828102.80 |
| 325 | 26037422.21 |
| 389 | 875519.05 |
| 495 | 20084.93 |
| 495 | 109850.46 |
+---------+-------------+
Then, what I'm looking for is:
然后，我正在寻找的是：
+---------+-------------+
| CLIENTE | MAX |
+---------+-------------+
| 64 | 1380690.74 |
| 187 | 1828468.71 |
| 325 | 26037422.21 |
| 389 | 875519.05 |
| 495 | 109850.46 |
+---------+-------------+
But running:
但是运行：
SELECT
clienteid AS "CLIENTE",
MAX(suma)
FROM
(SELECT clienteid, SUM(saldo) AS "suma" FROM etl.creditos
WHERE (mes_datos BETWEEN '2009-08-01' AND '2009-10-01') AND (titularidad_tipo LIKE 'TITULAR')
GROUP BY clienteid, nuc) AS sums
GROUP BY
clienteid
ORDER BY
clienteid;
Results as:
结果如下：
+---------+-------------+
| CLIENTE | SUMA |
+---------+-------------+
| 64 | 336879.21 |
| 187 | 1232824.51 |
| 325 | 3816173.62 |
| 389 | 218423.83 |
| 495 | 34105.99 |
+---------+-------------+
回答by DB_learner
SELECT ID, MAX(reg_sum)
FROM
(
SELECT ID, SUM(value) AS reg_sum FROM table
WHERE (date BETWEEN '2009-01-01' AND '2009-03-01')
GROUP BY ID, reg
) a GROUP by ID
回答by Ahmad Alhusainy
You can add [ order by SUM(value) DESC limit 1 ] to get the maximum value of the query results.
您可以添加 [ order by SUM(value) DESC limit 1 ] 以获取查询结果的最大值。
SELECT SUM(value) as maxcount FROM table WHERE (date BETWEEN '2009 01-01' AND '2009-03-01') GROUP BY reg order by maxcount desc limit 1;
