/*
-- :Flystone
*/-- Test Data: t1
If object_id('t1') is not null
Drop table t1
Go
Create table t1(id int,name varchar(13),total numeric(12,2))
Go
Insert into t1
select 1,'tom',3 union all
select 2,'benz',3 union all
select 3,'qq',1 union all
select 4,'tom',2.5
Go
-- Test Data: t2
If object_id('t2') is not null
Drop table t2
Go
Create table t2(id int,name varchar(14),total numeric(12,2),total1 numeric(12,2))
Go
Insert into t2
select 11,'benz',2,0 union all
select 12,'tom',1.5,0 union all
select 13,'tom',3,0 union all
select 14,'qq',6,0
Go
--Start
select a.id,a.name,a.total,
total1 = case when (select sum(total) from t2 where name = a.name and id <= a.id)< b.total
then 0 else
(select sum(total) from t2 where name = a.name and id <= a.id) - b.total
end
from t2 a right join
(select name,total from t1 where id = 4) b
on a.name = b.name--Result:
/*
id name total total1
----------- -------------- -------------- ----------------------------------------
12 tom 1.50 .00
13 tom 3.00 2.00(所影响的行数为 2 行)*/
--End
-- :Flystone
*/-- Test Data: t1
If object_id('t1') is not null
Drop table t1
Go
Create table t1(id int,name varchar(13),total numeric(12,2))
Go
Insert into t1
select 1,'tom',3 union all
select 2,'benz',3 union all
select 3,'qq',1 union all
select 4,'tom',2.5
Go
-- Test Data: t2
If object_id('t2') is not null
Drop table t2
Go
Create table t2(id int,name varchar(14),total numeric(12,2),total1 numeric(12,2))
Go
Insert into t2
select 11,'benz',2,0 union all
select 12,'tom',1.5,0 union all
select 13,'tom',3,0 union all
select 14,'qq',6,0
Go
--Start
select a.id,a.name,a.total,
total1 = case when (select sum(total) from t2 where name = a.name and id <= a.id)< b.total
then 0 else
(select sum(total) from t2 where name = a.name and id <= a.id) - b.total
end
from t2 a right join
(select name,total from t1 where id = 4) b
on a.name = b.name--Result:
/*
id name total total1
----------- -------------- -------------- ----------------------------------------
12 tom 1.50 .00
13 tom 3.00 2.00(所影响的行数为 2 行)*/
--End
解决方案 »
免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货