sql2000 依次扣费怎么做？

[gylmk]

表1
编号 金额
101 50
102 40
103 60
表2
编号 费用 月份
101 5 201001
101 6 201002
102 5 201001
103 5 201007
103 8 201008

要求，通过存储过程一次性扣除所有用户的费用
结果
表1
编号 金额
101 39
102 35
103 47

[cd731107]

update 表1 set 金额=金额-a.费用
from  (select sum(费用) as 费用 from 表2 group by 编号) tb
where 表1.编号=tb.编号

[cd731107]

update 表1 set 金额=金额-tb.费用
from  (select sum(费用) as 费用 from 表2 group by 编号) tb
where 表1.编号=tb.编号

[-晴天]

create table t1(编号 int,金额 int)
insert into t1 select 101,50
insert into t1 select 102,40
insert into t1 select 103,60
create table t2(编号 int,费用 int,月份 varchar(10))
insert into t2 select 101,5,'201001'
insert into t2 select 101,6,'201002'
insert into t2 select 102,5,'201001'
insert into t2 select 103,5,'201007'
insert into t2 select 103,8,'201008'
go
update a set 金额=金额-b.fy from t1 a inner join(
select 编号,sum(费用)fy from t2 group by 编号
)b on a.编号=b.编号

select * from t1
/*
编号          金额
----------- -----------
101         39
102         35
103         47

(3 行受影响)

*/
go
drop table t1,t2

[AcHerat]

update a 
set a.金额=金额-b.费用
from 表1 a join (select 编号,sum(费用) as 费用 from 表2 group by 编号) b on a.编号=b.编号

[SqlServer2008]

update 表1 set 金额=金额-费用  from 表1 left outer join 
(
select 编号 ,sum(费用) as 费用 from 表2 group by 编号 
)  t2 on 表1.编号=t2.编号

[gylmk]

你们的答案是都够扣费的情况。如果不够扣的呢。比如
表1
编号 金额
101 50
102 40
103 10
表2
编号 费用 月份
101 5 201001
101 6 201002
102 5 201001
103 5 201007
103 8 201008

[gylmk]

如果像上边说的这样。怎么让103只扣除1个月的费用？

[-晴天]

如果不够扣,那该怎么处理?

[gylmk]

在表2中增加个字段‘标志’，如果不够扣，在未扣费的记录上标记0，扣款过的标记1

[-晴天]

create table t1(编号 int,金额 int)
insert into t1 select 101,50
insert into t1 select 102,40
insert into t1 select 103,10
create table t2(编号 int,费用 int,月份 varchar(10))
insert into t2 select 101,5,'201001'
insert into t2 select 101,6,'201002'
insert into t2 select 102,5,'201001'
insert into t2 select 103,5,'201007'
insert into t2 select 103,8,'201008'
go
;with c1 as(
select row_number()over(partition by 编号 order by 月份)rn,编号,费用 from t2
),c2 as(
select b.rn,a.编号,a.金额-b.费用 金额 from t1 a inner join c1 b on a.编号=b.编号 where b.rn=1
union all
select b.rn,a.编号,a.金额-b.费用 金额 from c2 a inner join c1 b on a.编号=b.编号 and a.rn=b.rn-1 where a.金额-b.费用>=0
)select 编号,金额 from c2 a where not exists(select 1 from c2 where 编号=a.编号 and 金额<a.金额)
order by 编号


/*
编号          金额
----------- -----------
101         39
102         35
103         5

(3 行受影响)


*/
go
drop table t1,t2

[-晴天]

create table t1(编号 int,金额 int)
insert into t1 select 101,50
insert into t1 select 102,40
insert into t1 select 103,10
create table t2(编号 int,费用 int,月份 varchar(10))
insert into t2 select 101,5,'201001'
insert into t2 select 101,6,'201002'
insert into t2 select 102,5,'201001'
insert into t2 select 103,5,'201007'
insert into t2 select 103,8,'201008'
go
;with c1 as(
select row_number()over(partition by 编号 order by 月份)rn,编号,费用,月份 from t2
),c2 as(
select b.rn,a.编号,a.金额-b.费用 金额 from t1 a inner join c1 b on a.编号=b.编号 where b.rn=1
union all
select b.rn,a.编号,a.金额-b.费用 金额 from c2 a inner join c1 b on a.编号=b.编号 and a.rn=b.rn-1 where a.金额-b.费用>=0
)select a.编号,a.费用,a.月份,isnull(sign(b.金额),0) as 已扣 from c1 a left join c2 b on a.rn=b.rn and a.编号=b.编号
/*
编号          费用          月份         已扣
----------- ----------- ---------- -----------
101         5           201001     1
101         6           201002     1
102         5           201001     1
103         5           201007     1
103         8           201008     0

(5 行受影响)

*/
go
drop table t1,t2

[gylmk]

十分感谢 qianjin036a ，辛苦你了。还有个小问题，能给个qq号联系吗？qq 2650683

[gylmk]

我这里怎么在with这里出现附近语法错误？

[cd731107]

create table t1(编号 int,金额 int)
insert into t1 select 101,50
insert into t1 select 102,40
insert into t1 select 103,10
create table t2(编号 int,费用 int,月份 varchar(10),标志 bit)
insert into t2 select 101,5,'201001',0
insert into t2 select 101,6,'201002',0
insert into t2 select 102,5,'201001',0
insert into t2 select 103,5,'201007',0
insert into t2 select 103,8,'201008',0

update t2
set 标志=1
from  t1,
(select *,(select sum(费用) from t2 as tb where tb.编号=t2.编号 and tb.月份<=t2.月份)总费用 from t2) t3
where t2.编号=t3.编号 and t2.月份=t3.月份 and t1.编号=t3.编号 and t1.金额>t3.总费用

update t1 set 金额=金额-
(select top 1 总费用 from 
(select 编号,(select sum(费用) from t2 as tb where tb.编号=t2.编号 and tb.月份<=t2.月份)总费用 from t2 ) t3
where t1.编号=t3.编号 and t3.总费用<t1.金额 order by 总费用 desc)


select * from t1
select * from t2

drop table t1,t2

/*
编号          金额          
----------- ----------- 
101         39
102         35
103         5

（所影响的行数为 3 行）

编号          费用          月份         标志   
----------- ----------- ---------- ---- 
101         5           201001     1
101         6           201002     1
102         5           201001     1
103         5           201007     1
103         8           201008     0

（所影响的行数为 5 行）
*/