大家好,我有个问题问一下!
我想编辑adoquery数据集里的数据,根据每条记录的前两个字段来产生后一个字段的值!
开始查出的数据集是下面的样子:
is_lender d_money lend_money huan_money remain_money
1 50 0 0 0
-1 60 0 0 0
1 70 0 0 0
-1 80 0 0 0
经过运算要产生以下结果:
is_lender lend_money huan_money remain_money
1 50 0 50
-1 0 60 -10
1 70 0 60
-1 0 80 -20
判断,如果is_lender为1 则 lend_money=d_money ,
is_lender为-1 则huan_money=d_money
remain_money 等于上一条记录的remain_money+lend_money-huan_money
我想编辑adoquery数据集里的数据,根据每条记录的前两个字段来产生后一个字段的值!
开始查出的数据集是下面的样子:
is_lender d_money lend_money huan_money remain_money
1 50 0 0 0
-1 60 0 0 0
1 70 0 0 0
-1 80 0 0 0
经过运算要产生以下结果:
is_lender lend_money huan_money remain_money
1 50 0 50
-1 0 60 -10
1 70 0 60
-1 0 80 -20
判断,如果is_lender为1 则 lend_money=d_money ,
is_lender为-1 则huan_money=d_money
remain_money 等于上一条记录的remain_money+lend_money-huan_money
(id int identity(1,1),is_lender int, d_money int, lend_money int, huan_money int, remain_money int)
insert into #
select 1, 50 , 0 , 0 , 0 union all
select -1, 60 , 0 , 0 , 0 union all
select 1, 70 , 0 , 0 , 0 union all
select -1, 80 , 0 , 0 , 0select
is_lender,
case when is_lender=1 then d_money else 0 end as 'lend_money',
case when is_lender=-1 then d_money else 0 end as 'huan_money',
(select sum(d_money*is_lender) from #t where id<=a.id)as 'remain_money'
from # a/*
is_lender lend_money huan_money
----------- ----------- ----------- -----------
1 50 0 50
-1 0 60 -10
1 70 0 60
-1 0 80 -20(所影响的行数为 4 行)
*/
(id int identity(1,1),is_lender int, d_money int, lend_money int, huan_money int, remain_money int)
insert into #
select 1, 50 , 0 , 0 , 0 union all
select -1, 60 , 0 , 0 , 0 union all
select 1, 70 , 0 , 0 , 0 union all
select -1, 80 , 0 , 0 , 0select
is_lender,
case when is_lender=1 then d_money else 0 end as 'lend_money',
case when is_lender=-1 then d_money else 0 end as 'huan_money',
(select sum(d_money*is_lender) from #t where id<=a.id)as 'remain_money'
from # a/*
is_lender lend_money huan_money remain_money
----------- ----------- ----------- ------------
1 50 0 50
-1 0 60 -10
1 70 0 60
-1 0 80 -20(所影响的行数为 4 行)
*/
(id int identity(1,1),is_lender int, d_money int, lend_money int, huan_money int, remain_money int)
insert into #
select 1, 50 , 0 , 0 , 0 union all
select -1, 60 , 0 , 0 , 0 union all
select 1, 70 , 0 , 0 , 0 union all
select -1, 80 , 0 , 0 , 0select
is_lender,
case when is_lender=1 then d_money else 0 end as 'lend_money',
case when is_lender=-1 then d_money else 0 end as 'huan_money',
(select sum(d_money*is_lender) from # where id<=a.id)as 'remain_money'
from # a/*
is_lender lend_money huan_money remain_money
----------- ----------- ----------- ------------
1 50 0 50
-1 0 60 -10
1 70 0 60
-1 0 80 -20(所影响的行数为 4 行)
*/