CREATE TABLE TB_ACCOUNT_BUY (
ZID int(11) NOT NULL auto_increment,
ZUSER_ID int(11) default NULL,
ZDEPART_ID int(11) default NULL,
ZPROVIDER_ID int(11) default NULL,
ZCURRENCY_ID int(11) default '0',
ZBUYSUM decimal(14,4) default '0.0000',
ZRECEIVESUM decimal(14,4) default '0.0000',
ZRESTSUM decimal(14,4) default '0.0000',
PRIMARY KEY (`ZID`)
) ENGINE=InnoDB DEFAULT CHARSET=gbk ROW_FORMAT=COMPACT;
zid;ZUSER_ID ,ZDEPART_ID, ZPROVIDER_ID,ZCURRENCY_ID,ZBUYSUM,ZRECEIVESUM,ZRESTSUM1; 1, 1, 2 1 0, 0, 100
2, 3 3 , 2 1, 10 0 110
3 5, 5, 2 1, 0 20 90
ZRESTSUM=ZRESTSUM(上条记录)+ZBUYSUM-ZRECEIVESUM
我想在查询的时候计算出ZRESTSUM值如果插入的时候就计算ZRESTSUM,只要中间任何一个数据出错。
以后的ZRESTSUM都会是财务结果哪位给个思路
ZID int(11) NOT NULL auto_increment,
ZUSER_ID int(11) default NULL,
ZDEPART_ID int(11) default NULL,
ZPROVIDER_ID int(11) default NULL,
ZCURRENCY_ID int(11) default '0',
ZBUYSUM decimal(14,4) default '0.0000',
ZRECEIVESUM decimal(14,4) default '0.0000',
ZRESTSUM decimal(14,4) default '0.0000',
PRIMARY KEY (`ZID`)
) ENGINE=InnoDB DEFAULT CHARSET=gbk ROW_FORMAT=COMPACT;
zid;ZUSER_ID ,ZDEPART_ID, ZPROVIDER_ID,ZCURRENCY_ID,ZBUYSUM,ZRECEIVESUM,ZRESTSUM1; 1, 1, 2 1 0, 0, 100
2, 3 3 , 2 1, 10 0 110
3 5, 5, 2 1, 0 20 90
ZRESTSUM=ZRESTSUM(上条记录)+ZBUYSUM-ZRECEIVESUM
我想在查询的时候计算出ZRESTSUM值如果插入的时候就计算ZRESTSUM,只要中间任何一个数据出错。
以后的ZRESTSUM都会是财务结果哪位给个思路
set nocount on
create table test(zid varchar(20),ZUSER_ID varchar(20),ZDEPART_ID varchar(20),
ZPROVIDER_ID varchar(20),ZCURRENCY_ID varchar(20),ZBUYSUM int,ZRECEIVESUM int ,ZRESTSUM int)
insert into test select '1','1','1','2','1','0','0','100'
insert into test select '2','3','3','2','1,','10','0','110'
insert into test select '3','5','5','2','1,','0','20','90'
go
--测试
select *,result=isnull((select ZRESTSUM from test where zid=a.zid-1),0)+ZBUYSUM-ZRECEIVESUM
from test a
--删除测试环境
drop table test
set nocount off