mysql - SQL中如何求累加和扣除

Question

我在 mysql 表中有以下数据，

+------------+--------+--------+
| date       | inQty  | outQty |
+------------+--------+--------+
| 2011-10-24 | 700.00 | 0.0    |
| 2011-10-01 | 500.00 | 0.0    |
| 2011-10-02 | 500.00 | 0.0    |
| 2011-10-03 | 550.00 | 0.0    |
| 2011-10-04 | 100.00 | 0.0    |
| 2011-10-05 | 200.00 | 0.0    |
| 2011-10-05 |   0.00 | 100.0  |
| 2011-10-02 |   0.00 | 500.0  |
| 2011-10-03 |   0.00 | 150.0  |
| 2011-10-24 | 200.00 | 0.0    |
+------------+--------+--------+

从上表中我需要以下结果，

+------------+--------+---------+----------+
| tDate      | tInQty | tOutQty | tbalance |
+------------+--------+---------+----------+
| 2011-10-01 | 500.00 |    0.00 |   500.00 | 
| 2011-10-02 | 500.00 |    0.00 |   1000.00| ->> (tInQty + tInQty)
| 2011-10-02 |   0.00 |  500.00 |   500.00 |->> (tInQty - tOutQty)
| 2011-10-03 | 550.00 |    0.00 |   1050.00|
| 2011-10-03 |   0.00 |  150.00 |   900.00 |
| 2011-10-04 | 100.00 |    0.00 |   1000.99|
| 2011-10-05 | 200.00 |    0.00 |   1200.99|
| 2011-10-05 |   0.00 |  100.00 |   1100.00|
| 2011-10-24 | 700.00 |    0.00 |   1800.00|
| 2011-10-24 | 200.00 |    0.00 |   200.00 |
+------------+--------+---------+----------+

为了获得上述结果，我编写了以下存储过程，

-- --------------------------------------------------------------------------------
-- Routine DDL
-- --------------------------------------------------------------------------------
DELIMITER $$

CREATE DEFINER=`root`@`localhost` PROCEDURE `get_balance`()
BEGIN




DECLARE vDate DATE DEFAULT '0000-00-00';
DECLARE vInQty DECIMAL(5,2) DEFAULT 0.0;
DECLARE vOutQty DECIMAL(5,2) DEFAULT 0.0;
DECLARE balance DECIMAL(5,2) DEFAULT 0.0;


DECLARE vvDate INT;
DECLARE vSum DECIMAL(5,2) DEFAULT 0.0;

DECLARE flag INT DEFAULT 0;

DECLARE tCursor CURSOR FOR SELECT * FROM `mydatabase`.`new_table` ORDER BY date;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET flag = 1;


DROP TABLE IF EXISTS temp_table;

CREATE TEMPORARY TABLE temp_table(
    tDate DATE NOT NULL DEFAULT '0000-00-00',
    tInQty DECIMAL(5,2) DEFAULT 0.0,
    tOutQty DECIMAL(5,2) DEFAULT 0.0,
    tbalance DECIMAL(5,2) DEFAULT 0.0
);



OPEN tCursor;
SET balance = 0.0;


REPEAT
FETCH tCursor INTO vDate, vInQty, vOutQty;
SELECT COUNT(vDate) INTO vvDate FROM temp_table WHERE vDate = tDate;

IF vvDate = 0 THEN

    CASE
        WHEN vInQty != 0.0 THEN

            SET balance = balance + vInQty;
            INSERT INTO temp_table VALUES(vDate, vInQty, vOutQty, balance);


        WHEN vOutQty != 0.0 THEN
            SET balance = balance - vOutQty;
            INSERT INTO temp_table VALUES(vDate, vInQty, vOutQty, balance);

        ELSE
            SET vSum = 0.0;

    END CASE; -- end of case

ELSEIF vvDate > 0 THEN
    CASE

        WHEN vInQty != 0.0 THEN
            SET balance = balance + vInQty;
            INSERT INTO temp_table VALUES(vDate, vInQty, vOutQty, vInQty);

        WHEN vOutQty != 0.0 THEN
            SET balance = balance + vOutQty;
            INSERT INTO temp_table VALUES(vDate, vInQty, vOutQty, balance);

        ELSE
            SET vSum = 0.0;
    END CASE; -- end of case
END IF;

UNTIL flag END REPEAT; -- End of repeat

CLOSE tCursor;


SELECT * FROM temp_table;

END

但是，当我运行上面的存储过程时，我得到以下结果，如何获得我期望的结果？

+------------+--------+---------+----------+
| tDate      | tInQty | tOutQty | tbalance |
+------------+--------+---------+----------+
| 2011-10-01 | 500.00 |    0.00 |   500.00 |
| 2011-10-02 | 500.00 |    0.00 |   999.99 |
| 2011-10-02 |   0.00 |  500.00 |   999.99 |
| 2011-10-03 | 550.00 |    0.00 |   999.99 |
| 2011-10-03 |   0.00 |  150.00 |   999.99 |
| 2011-10-04 | 100.00 |    0.00 |   999.99 |
| 2011-10-05 | 200.00 |    0.00 |   999.99 |
| 2011-10-05 |   0.00 |  100.00 |   999.99 |
| 2011-10-24 | 700.00 |    0.00 |   999.99 |
| 2011-10-24 | 200.00 |    0.00 |   200.00 |
| 2011-10-24 | 200.00 |    0.00 |   200.00 |
+------------+--------+---------+----------+

Answer 1

首先，我认为您想要的结果是:

+------------+--------+---------+----------+
| tDate      | tInQty | tOutQty | tbalance |
+------------+--------+---------+----------+
| 2011-10-01 | 500.00 |    0.00 |   500.00 | 
| 2011-10-02 | 500.00 |    0.00 |   1000.00| ->> (tInQty + tInQty)
| 2011-10-02 |   0.00 |  500.00 |   500.00 |->> (tInQty - tOutQty)
| 2011-10-03 | 550.00 |    0.00 |   1050.00|
| 2011-10-03 |   0.00 |  150.00 |   900.00 |
| 2011-10-04 | 100.00 |    0.00 |   1000.00|
| 2011-10-05 | 200.00 |    0.00 |   1200.00|
| 2011-10-05 |   0.00 |  100.00 |   1100.00|
| 2011-10-24 | 700.00 |    0.00 |   1800.00|
| 2011-10-24 | 200.00 |    0.00 |   2000.00|
+------------+--------+---------+----------+

为了实现这一点，您不必编写存储过程。它可以通过使用来实现 MySQL user defined variables 。例如:

create table dateCumulative
(vDate date not null,
inQty decimal (12,2) not null default 0.0,
outQty decimal (12,2) not null default 0.0
);

insert into dateCumulative values ('2011-10-24',700.00,0.0);
insert into dateCumulative values ('2011-10-01',500.00,0.0);
insert into dateCumulative values ('2011-10-02',500.00,0.0);
insert into dateCumulative values ('2011-10-03',550.00,0.0);
insert into dateCumulative values ('2011-10-04',100.00,0.0);
insert into dateCumulative values ('2011-10-05',200.00,0.0);
insert into dateCumulative values ('2011-10-05',0.00,100.0);
insert into dateCumulative values ('2011-10-02',0.00,500.0);
insert into dateCumulative values ('2011-10-03',  0.00    ,150.0);
insert into dateCumulative values ('2011-10-24',  200.00  ,0.0);

select t.vDate,t.inQty,t.outQty,
round(t.inQtySum-t.outQtySum,2) as balance
from
( 
select 
@inCsum := @inCsum + inQty as inQtySum,
@outCsum := @outCsum + outQty as outQtySum,
dc.*
from dateCumulative dc
cross join (SELECT @curDate := '1970-01-01',@inCsum:=0,@outCsum:=0) as t
order by vDate asc
) t;