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
解决方案 »
- ERROR 1048 (23000): Column 'col' cannot be null 问题
- 请教高手
- mysql创建表时,default约束后面是字符串要不要加引号?
- union all效率低下,该用存储过程、视图、临时表哪种方法来解决?
- xtranbackup1.6的增量备份为什么 建的新表增量失效?
- mysql关于update的问题
- Cannot load JDBC driver class 'com.mysql.jdbc.Driver'
- 可以用ASP访问mysql吗~请告知~thx
- 推荐几本学习SQL Server的好书吧!
- 关于hibernate添加纪录的问题
- SQL语句
- SQL查询
+---------------------+---------------+-------------+-----------+
| 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>