mysql - MySQL中如何根据日期进行累计计数？

Question

我有下表“testfinal”，希望使其看起来像表“testfinal1”，并根据订单日期进行累加。

最终测试

+------+------------+-------+--------+----------+
| ID   | Orderdate  | apple | banana | quantity |
+------+------------+-------+--------+----------+
| 1005 | 2015-05-05 |     2 |      0 |        2 |
| 1005 | 2015-05-05 |     0 |      2 |        2 |
| 1005 | 2015-05-06 |     0 |      1 |        1 |
| 1006 | 2011-05-06 |     0 |      3 |        3 |
| 1006 | 2011-10-06 |     1 |      0 |        1 |
+------+------------+-------+--------+----------+

最终测试1

+------+------------+-------+--------+----------+
| ID   | Orderdate  | apple | banana | quantity |
+------+------------+-------+--------+----------+
| 1005 | 2015-05-05 |     2 |      2 |        4 |
| 1005 | 2015-05-06 |     2 |      3 |        5 |
| 1006 | 2011-05-06 |     0 |      3 |        3 |
| 1006 | 2011-10-06 |     1 |      3 |        4 |
+------+------------+-------+--------+----------+

现在我的代码如下，但它不起作用

insert into testfinal1 (ID, Orderdate, apple, banana, quantity) 
select ID, Orderdate, 
(select sum(apple)from testfinal where date_format(OrderDate, '%Y-%m-%d') <= date_format(OrderDate, '%Y-%m-%d' ))  as apple, 
(select sum(banana)from testfinal where date_format(OrderDate, '%Y-%m-%d') <= date_format(OrderDate, '%Y-%m-%d' ))  as banana, 
(select sum(quantity)from testfinal where date_format(OrderDate, '%Y-%m-%d') <= date_format(OrderDate, '%Y-%m-%d' ))  as quantity 
from testfinal group by ID, Orderdate;

我认为问题是未指定术语 orderdate。

Answer 1

这是您可能需要首先生成的查询:

SELECT
ID,
Orderdate,
SUM(apple) AS 'apple',
SUM(banana) AS 'banana',
SUM(quantity) AS 'quantity'
FROM testfinal
GROUP BY Orderdate;

<小时/>

DROP TABLE IF EXISTS testfinal1;

CREATE TABLE testfinal1 LIKE testfinal;

INSERT INTO testfinal1(ID,Orderdate,apple,banana,quantity)

SELECT
ID,
Orderdate,
SUM(apple) AS 'apple',
SUM(banana) AS 'banana',
SUM(quantity) AS 'quantity'
FROM testfinal
GROUP BY Orderdate;