我有下表：
+-------------+-------+-------+
| date | agent | hold |
+-------------+-------+-------+
| 2017-01-01 | A | 100 |
| 2017-01-01 | B | 200 |
| 2017-01-02 | C | 400 |
+-------------+-------+-------+
我的查询添加了一个运行总计：
SET @runtot:=0;
SELECT
date,
SUM(CASE WHEN agent = 'A' THEN hold ELSE 0 END) AS A,
SUM(CASE WHEN agent = 'B' THEN hold ELSE 0 END) AS B,
SUM(CASE WHEN agent = 'C' THEN hold ELSE 0 END) AS C,
SUM(CASE WHEN agent = 'D' THEN hold ELSE 0 END) AS D,
sum(hold) as Total,
(@runtot := @runtot + sum(hold)) AS rt
FROM daily_results
group by date
但是我在rt列中得到的总和是每一行的总和。我究竟做错了什么？
最佳答案
按照建议使用汇总，或者将集合体移动到子查询，然后在主查询中进行总运行。例如
给定
+------------+------------+---------+
| tanggal | iddowntime | idshift |
+------------+------------+---------+
| 2017-10-03 | MOR01 | 1 |
| 2017-10-03 | NF001 | 2 |
| 2017-10-03 | MOR01 | 2 |
| 2017-10-02 | MOR01 | 2 |
| 2017-10-03 | NF001 | 2 |
| 2017-10-02 | MOR01 | 1 |
| 2017-10-02 | NF001 | 1 |
| 2017-10-02 | NF001 | 1 |
+------------+------------+---------+
8 rows in set (0.00 sec)
select s.*,
@rt:=@rt + total RunningTotal
from
(
SELECT
tanggal,
SUM(CASE WHEN iddowntime = 'mor01' THEN idshift ELSE 0 END) AS A,
SUM(CASE WHEN iddowntime = 'nf001' THEN idshift ELSE 0 END) AS B,
#SUM(CASE WHEN agent = 'C' THEN hold ELSE 0 END) AS C,
#SUM(CASE WHEN agent = 'D' THEN hold ELSE 0 END) AS D,
sum(idshift) as Total
FROM trans_lhpdtdw
where iddowntime in('mor01','nf001')
group by tanggal
) s
cross join
(select @rt:=0) rt
+------------+------+------+-------+--------------+
| tanggal | A | B | Total | RunningTotal |
+------------+------+------+-------+--------------+
| 2017-10-02 | 3 | 2 | 5 | 5 |
| 2017-10-03 | 3 | 4 | 7 | 12 |
+------------+------+------+-------+--------------+
2 rows in set (0.01 sec)
关于mysql - 运行总MySQL显示行的总和，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/50045964/