有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可以这样求select sum(cnt) from 表2 where sldat between '2011-01-01' and '2012-12-31'如何将表2和表3的cnt进行计算?
表1,表2,表3,
表1为主表, 表2和表3为明细表,
3个表都以 vid为外键,且唯一表2和表3都有相同字段 sldat来确定记录日期, cnt来记录值其中,表2中的cnt字段,可以用sum(cnt)来计算,
表3中的cnt字段,正数值为 减 负数值为 加
需要更新的是表1中的EST字段,
问题来了:表3中的cnt值怎么计算?表2中的cnt可以这样求select sum(cnt) from 表2 where sldat between '2011-01-01' and '2012-12-31'如何将表2和表3的cnt进行计算?
(
select sum(cnt) from basvipcnt t2 where t2.sldat BETWEEN '2011-01-01' and '2012-12-31' and t2.vipid = t1.vipid
)
+
(
select sum(cnt) from basvipuse t3 where t3.sldat BETWEEN '2011-02-01' and '2012-12-31' and t3.vipid = t1.vipid and cnt < 0;
)
-
(
select sum(cnt) from basvipuse t4 where t4.sldat BETWEEN '2011-02-01' and '2012-12-31' and t4.vipid = t1.vipid and cnt > 0;
)
where t1.vid = t2.vid AND t1.vid = t3.vid AND t1.vid = t4.vid;
(select
cnt = (case when t2.cnt is null then 0 else t2.cnt end)+(case when t4.cnt is null then 0 else t4.cnt end)
-(case when t3.cnt is null then 0 else t3.cnt end)
from
(select vipid,sum(cnt) from basvipcnt where sldat BETWEEN '2011-01-01' and '2012-12-31') t2,
(select vipid,sum(cnt) from basvipuse where sldat between '2011-02-01' and '2012-12-31' and cnt>0 )t3,
(select vipid,sum(cnt) from basvipuse where sldat between '2011-02-01' and '2012-12-31' and cnt<0 )t4
where
t1.vipid=t2.vipid and t2.vipid=t3.vipid and t3.vipid=t4.vipid
思路基本成型求完整语法
create table A(aa int,ID int)
insert A
select 1,1 union all
select 1,-2 union all
select 2,-3
go
select aa, sum(ID) from A
group by aa
/*
aa 无列名
-- ---
1 -1
2 -3
*/
go
drop table A
update basvipnew set rest=(select
cnt = sum(case when t2.cnt is null then 0 else t2.cnt end)+sum(case when t4.cnt is null then 0 else t4.cnt end)
-sum(case when t3.cnt is null then 0 else t3.cnt end)
from
basvipnew t1,
(select vipid,cnt from basvipcnt where sldat BETWEEN '2011-01-01' and '2012-12-31') t2,
(select vipid,cnt from basvipuse where sldat between '2011-02-01' and '2012-12-31' and cnt>0 )t3,
(select vipid,cnt from basvipuse where sldat between '2011-02-01' and '2012-12-31' and cnt<0 )t4
where
t1.vipid=t2.vipid and t2.vipid=t3.vipid and t3.vipid=t4.vipid) 但是这样更新就更新全部了......这个update..哎..头大
cnt = sum(case when t2.cnt is null then 0 else t2.cnt end)+sum(case when t4.cnt is null then 0 else t4.cnt end)
-sum(case when t3.cnt is null then 0 else t3.cnt end)
from
basvipnew t1,
(select vipid,cnt from basvipcnt where sldat BETWEEN '2011-01-01' and '2012-12-31') t2,
(select vipid,cnt from basvipuse where sldat between '2011-02-01' and '2012-12-31' and cnt>0 )t3,
(select vipid,cnt from basvipuse where sldat between '2011-02-01' and '2012-12-31' and cnt<0 )t4
where
t1.vipid=t2.vipid and t2.vipid=t3.vipid and t3.vipid=t4.vipid)
where sldat between '2011-02-01' and '2012-12-31'没写条件。
cnt = sum(case when t2.cnt is null then 0 else t2.cnt end)+sum(case when t4.cnt is null then 0 else t4.cnt end)
-sum(case when t3.cnt is null then 0 else t3.cnt end)
into #t
from
basvipnew t1,
(select vipid,cnt from basvipcnt where sldat BETWEEN '2011-01-01' and '2012-12-31') t2,
(select vipid,cnt from basvipuse where sldat between '2011-02-01' and '2012-12-31' and cnt>0 )t3,
(select vipid,cnt from basvipuse where sldat between '2011-02-01' and '2012-12-31' and cnt<0 )t4
where
t1.vipid=t2.vipid and t2.vipid=t3.vipid and t3.vipid=t4.vipidupdate basvipnew set rest=#t.cnt from basvipnew inner join #t on basvipnew .vipid=#t.vipid
declare @basvipnew table (vipid int,vipname varchar(5),rest int)
insert into @basvipnew
select 1,'tom',0 union all
select 2,'name',1 union all
select 3,'jerry',1declare @basvipcnt table (vipid int,cnt int,sldat datetime)
insert into @basvipcnt
select 1,5,'2011-03-01' union all
select 1,6,'2011-02-03' union all
select 2,1,'2011-09-08' union all
select 2,10,'2011-11-01' union all
select 3,2,'2011-08-07'declare @basvipuse table (vipid int,cnt int,sldat datetime)
insert into @basvipuse
select 1,-3,'2011-03-01' union all
select 1,-7,'2011-02-03' union all
select 2,-12,'2011-09-08' union all
select 2,-10,'2011-11-01' union all
select 3,-22,'2011-08-07'update @basvipnew
set rest=b.cnt+c.cnt from @basvipnew a
left 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.vipid
left 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 21
2 name 33
3 jerry 24
*//*
如果正负积分都加的话,用绝对值就ok了。
如果正的是减,负的是加,sum然后求相反数就ok了。
*/