I have a table with dates and follower growth for each date and want to make a new column that depicts total followers gained.
我有一个表格,其中包含每个日期的日期和关注者增长情况,并希望制作一个新列,描述获得的关注者总数。
How can I insert a new column that basically adds the 'new followers' column in to a summation series?
如何插入一个新列,基本上将“新关注者”列添加到求和系列中?
date | new followers
-----------------------
1/1/15 | 1
1/2/15 | 3
1/3/15 | 5
1/4/15 | 4
1/5/15 | 3
New table would look like
新表看起来像
date | new followers | total followers gained
------------------------------------------------
1/1/15 | 1 | 1
1/2/15 | 3 | 4
1/3/15 | 5 | 9
1/4/15 | 4 | 13
1/5/15 | 3 | 16
1 个解决方案
#1
This SQL will accomplish what you want with a Select. If it is really necessary to persist this value in a table, then you could use this SQL in a trigger. But since you could just calculate this amount on the fly, I'm not sure why you would persist it.
这个SQL将通过Select实现您想要的。如果确实需要在表中保留此值,则可以在触发器中使用此SQL。但既然你可以动态计算这个数额,我不确定为什么你会坚持下去。
declare @tempTable table (theDt datetime, newFollowers int)
insert into @tempTable
select '1/1/15',1
UNION select '1/2/15',3
UNION select '1/3/15',5
UNION select '1/4/15',4
UNION select '1/5/15',3
select t1.theDt,
t1.newFollowers + SUM(case when t2.theDt IS not null then t2.newFollowers else 0 end) as "Total Followers"
from @tempTable t1
left outer join @tempTable t2 on t1.theDt > t2.theDt
group by t1.theDt, t1.newFollowers
order by t1.theDt
#1
This SQL will accomplish what you want with a Select. If it is really necessary to persist this value in a table, then you could use this SQL in a trigger. But since you could just calculate this amount on the fly, I'm not sure why you would persist it.
这个SQL将通过Select实现您想要的。如果确实需要在表中保留此值,则可以在触发器中使用此SQL。但既然你可以动态计算这个数额,我不确定为什么你会坚持下去。
declare @tempTable table (theDt datetime, newFollowers int)
insert into @tempTable
select '1/1/15',1
UNION select '1/2/15',3
UNION select '1/3/15',5
UNION select '1/4/15',4
UNION select '1/5/15',3
select t1.theDt,
t1.newFollowers + SUM(case when t2.theDt IS not null then t2.newFollowers else 0 end) as "Total Followers"
from @tempTable t1
left outer join @tempTable t2 on t1.theDt > t2.theDt
group by t1.theDt, t1.newFollowers
order by t1.theDt