有3个表,
表1,表2,表3,
表1为主表, 表2和表3为明细表,
3个表都以 vid为外键,且唯一
表2和表3都有相同字段 sldat来确定记录日期, cnt来记录值
其中,表2中的cnt字段,可以用sum(cnt)来计算,
表3中的cnt字段,正数值为 减 负数值为 加
需要更新的是表1中的EST字段,
问题来了:
表3中的cnt值怎么计算?
表2中的cnt可以这样求
- SQL code
select sum(cnt) from 表2 where sldat between '2011-01-01' and '2012-12-31'
如何将表2和表3的cnt进行计算?
------解决方案--------------------
- SQL code
declare @basvipnew table (vipid int,vipname varchar(5),rest int)insert into @basvipnewselect 1,'tom',0 union allselect 2,'name',1 union allselect 3,'jerry',1declare @basvipcnt table (vipid int,cnt int,sldat datetime)insert into @basvipcntselect 1,5,'2011-03-01' union allselect 1,6,'2011-02-03' union allselect 2,1,'2011-09-08' union allselect 2,10,'2011-11-01' union allselect 3,2,'2011-08-07'declare @basvipuse table (vipid int,cnt int,sldat datetime)insert into @basvipuseselect 1,-3,'2011-03-01' union allselect 1,-7,'2011-02-03' union allselect 2,-12,'2011-09-08' union allselect 2,-10,'2011-11-01' union allselect 3,-22,'2011-08-07'update @basvipnewset rest=b.cnt+c.cnt from @basvipnew aleft join (select vipid,sum(cnt) as cnt from @basvipcnt where sldat between '2011-01-01' and '2011-12-31' group by vipid) b on a.vipid=b.vipidleft join (select vipid,sum(abs(cnt)) as cnt from @basvipuse where sldat between '2011-01-01' and '2011-12-31' group by vipid) c on a.vipid=c.vipidselect * from @basvipnew/*vipid vipname rest----------- ------- -----------1 tom 212 name 333 jerry 24*//*如果正负积分都加的话，用绝对值就ok了。如果正的是减，负的是加，sum然后求相反数就ok了。*/