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
头晕,
'2010-12-06 09:36:45', 'INWARD CR-IBG', 1, 2000.00, 2000.00 '1 Balance:2000
'2010-12-06 09:43:28', 'Paid', 2, 1000.0000, 1000.00 '2 Balance:2000-1000=1000
'2010-12-06 09:50:04', 'INWARD CR-IBG', 1, 3000.0000, 4000.00 '3: Balance:1000+3000=4000
,SUM(IF(B.transstatus=1,B.amount,-1*B.amount)) AS Balance FROM TEST A1
LEFT JOIN TEST B ON A1.transdate>=B.transdate
GROUP BY A1.transdate,A1.description,A1.transstatus,A1.amount