update a
set jiang_month=b.jiang_month,
jiangleft=a.jiangleft+b.jiangleft
from user1 a
inner join #user_week b
on a.dm=b.dm
set jiang_month=b.jiang_month,
jiangleft=a.jiangleft+b.jiangleft
from user1 a
inner join #user_week b
on a.dm=b.dm
调试欢乐多
AS
begin
select dm,
jiang_month = ISNULL(jiang2,0) + ISNULL(jiang31,0) + ISNULL(jiang4,0) + ISNULL(jiang41,0) + ISNULL(jiang6,0) + ISNULL(iang7,0) + ISNULL(iang_partner,0) + ISNULL(jiangleft,0) - ISNULL(jiang77,0),
jiangleft = case when ISNULL(jiang2,0) + ISNULL(jiang31,0) + ISNULL(jiang4,0) + ISNULL(jiang41,0) + ISNULL(jiang6,0) + ISNULL(jiang7,0) + ISNULL(jiang_partner,0) + ISNULL(jiangleft,0) - ISNULL(jiang77,0) < 100
then ISNULL(jiang2,0) + ISNULL(jiang31,0) + ISNULL(jiang4,0) + ISNULL(jiang41,0) + ISNULL(jiang6,0) + ISNULL(jiang7,0) + ISNULL(jiang_partner,0) + ISNULL(jiangleft,0) - ISNULL(jiang77,0)
else 0
end
into #user_week
from award where ci=@ciBEGIN TRAN
update a
set a.jiang_month = b.jiang_month,
a.jiangleft = a.jiangleft + b.jiangleft
from user1 a ,#user_week b
where a.dm=b.dm
COMMIT TRAN
end
set user1.jiang_month=Award.jiang2+Award.jiang31+Award.jiang4+Award.jiang41+Award.jiang6+Award.jiang7+Award.jiang_partner+Award.jiangleft-Award.jiang77
,
a.jiangleft = a.jiangleft + case when Award.jiang2+Award.jiang31+Award.jiang4+Award.jiang41+Award.jiang6+Award.jiang7+Award.jiang_partner+Award.jiangleft-Award.jiang77<100 then Award.jiang2+Award.jiang31+Award.jiang4+Award.jiang41+Award.jiang6+Award.jiang7+Award.jiang_partner+Award.jiangleft-Award.jiang77 else 0 end
from Award
where ci=@ci and Award.dm = user1.dm--如果相加的字段有NULL值要用isnull(字段,0)来转换