使用 MySQL 汇总加法 [英] Rolling up addition using MySQL

问题描述

我想通过添加前一行值分数来添加第三列或修改第二列.我正在按日期添加两个表，但无法连续添加数据.

I would like to add third column or modify second column by addition of previous row value score. I'm getting date-wise two table addition but can't get serial addition of data.

DDL:

CREATE TABLE 1_bugs 
( id int(11) NOT NULL
, date date NOT NULL
, cf1 int(11) NOT NULL
, cf2 int(11) NOT NULL 
) ENGINE=InnoDB DEFAULT CHARSET=latin1; 

INSERT INTO 1_bugs (id, date, cf1, cf2) VALUES 
(1, '2016-07-19', 3, 2), 
(2, '2016-07-19', 2, 1), 
(3, '2016-07-22', 2, 2);

查询:

SELECT table.date1, IFNULL(table.cf1 + bugs.cf2),0) as score
FROM table GROUP BY table.date1;

输出:

| date1 | score  |
| 2016-07-19 | 5 |
| 2016-07-19 | 3 |
| 2016-07-22 | 4 |

预期:

| date1 | score  | Total score  |
| 2016-07-19 | 5 | 5 |
| 2016-07-19 | 3 | 8 |
| 2016-07-22 | 4 | 12 |

我尝试过汇总，但它没有按预期提供输出，只是添加了空行并添加了所有分值.

I have tried rollup but it doesn't give output as expected and just adds null row with addition of all score values.

| date1 | score  |
| 2016-07-19 | 5 |
| 2016-07-19 | 3 |
| 2016-07-22 | 4 |
| null       | 12 |

如何获得预期的输出?

推荐答案

SELECT x.*
     , x.cf1+x.cf2 sub_total
     , SUM(y.cf1+y.cf2) running
  FROM 1_bugs x 
  JOIN 1_bugs y 
    ON y.id <= x.id 
 GROUP 
    BY x.id;
+----+------------+-----+-----+-----------+---------+
| id | date       | cf1 | cf2 | sub_total | running |
+----+------------+-----+-----+-----------+---------+
|  1 | 2016-07-19 |   3 |   2 |         5 |       5 |
|  2 | 2016-07-19 |   2 |   1 |         3 |       8 |
|  3 | 2016-07-22 |   2 |   2 |         4 |      12 |
+----+------------+-----+-----+-----------+---------+

这篇关于使用 MySQL 汇总加法的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！