Create table test (
transdate datetime default null,
description varchar(16) default null,
transstatus integer default 0,
amount decimal(19,4) default null);insert into test value ('2010-12-06 09:36:45', 'INWARD CR-IBG', 1, 2000.00),
('2010-12-06 09:43:28', 'Paid', 2, 1000.00),
('2010-12-06 09:50:04', 'INWARD CR-IBG', 1, 3000.00);算出他们的BALANCE,1是进,2是出。
结果是:
'2010-12-06 09:36:45', 'INWARD CR-IBG', 1, 2000.00, 2000.00
'2010-12-06 09:43:28', 'Paid', 2, 1000.0000, 1000.00
'2010-12-06 09:50:04', 'INWARD CR-IBG', 1, 3000.0000, 4000.00
头晕,
transdate datetime default null,
description varchar(16) default null,
transstatus integer default 0,
amount decimal(19,4) default null);insert into test value ('2010-12-06 09:36:45', 'INWARD CR-IBG', 1, 2000.00),
('2010-12-06 09:43:28', 'Paid', 2, 1000.00),
('2010-12-06 09:50:04', 'INWARD CR-IBG', 1, 3000.00);算出他们的BALANCE,1是进,2是出。
结果是:
'2010-12-06 09:36:45', 'INWARD CR-IBG', 1, 2000.00, 2000.00
'2010-12-06 09:43:28', 'Paid', 2, 1000.0000, 1000.00
'2010-12-06 09:50:04', 'INWARD CR-IBG', 1, 3000.0000, 4000.00
头晕,
+---------------------+---------------+-------------+-----------+
| transdate | description | transstatus | amount |
+---------------------+---------------+-------------+-----------+
| 2010-12-06 09:36:45 | INWARD CR-IBG | 1 | 2000.0000 |
| 2010-12-06 09:43:28 | Paid | 2 | 1000.0000 |
| 2010-12-06 09:50:04 | INWARD CR-IBG | 1 | 3000.0000 |
+---------------------+---------------+-------------+-----------+
3 rows in set (0.03 sec)mysql> select *,
-> (select sum(amount*if(transstatus=1,1,-1)) From test where transdate<=a.transdate) AS balance
-> from test a;
+---------------------+---------------+-------------+-----------+-----------+
| transdate | description | transstatus | amount | balance |
+---------------------+---------------+-------------+-----------+-----------+
| 2010-12-06 09:36:45 | INWARD CR-IBG | 1 | 2000.0000 | 2000.0000 |
| 2010-12-06 09:43:28 | Paid | 2 | 1000.0000 | 1000.0000 |
| 2010-12-06 09:50:04 | INWARD CR-IBG | 1 | 3000.0000 | 4000.0000 |
+---------------------+---------------+-------------+-----------+-----------+
3 rows in set (0.01 sec)mysql>