select b.AccountID, MAX(b.FollowersCount) as FollowersSum, (MAX(b.FollowersCount) - MAX(t.FollowersCount)) as NewFollowers from WeiboStaticits b, WeiboStaticits as t where 1=1 and b.AccountID = t.AccountID and b.CreateOn < GETDATE() and t.CreateOn < '2013-12-26' group by b.AccountID 这是之前写的 关注数只增不减的话 用max可以解决
第一,增加一个自动增长列PKID,有助于判断是否是区间内的最新数据CREATE TABLE WeiboStaticits( PKID int identity(1,1) not null, [AccountID] [varchar](50) NOT NULL, --微博账号 [FollowersCount] [decimal](18, 0) NOT NULL, --总关注数 [CreateOn] [datetime] NOT NULL )第二,SQL仅供参考,期待更优化的写法 select tMax.AccountID,tMax.FollowersCount - tMin.FollowersCount as NewFollowers, tMax.FollowersCount as FollowersSum from (select PKID,AccountID,FollowersCount from WeiboStaticits where PKID in(select max(PKID) as PKID from WeiboStaticits where CreateOn < GETDATE() and CreateOn > '2013-12-15' group by AccountID)) tMax inner join (select PKID,AccountID,FollowersCount from WeiboStaticits where PKID in (select min(PKID) as PKID from WeiboStaticits where CreateOn < GETDATE() and CreateOn > '2013-12-15' group by AccountID)) tMin on tMax.AccountID=tMin.AccountID
select b.AccountID,
MAX(b.FollowersCount) as FollowersSum,
(MAX(b.FollowersCount) - MAX(t.FollowersCount)) as NewFollowers
from WeiboStaticits b, WeiboStaticits as t where 1=1 and b.AccountID = t.AccountID
and b.CreateOn < GETDATE() and t.CreateOn < '2013-12-26' group by b.AccountID
这是之前写的 关注数只增不减的话 用max可以解决
zhangsan这个账号最新更新的数据是2013-12-25 关注数为22
lisi是2013-11-25 关注数为55注:计算新增关注数以当前日期与参数时间作比较我希望最后得到的结果是这样AccountID FollowersSum FollowersIncrease
--------- -------------- -----------------
zhangsan 22 -3
lisi 55 5
参数日期:2013-12-01
则zhangsan的数据不符合条件 仅统计lisi的AccountID FollowersSum FollowersIncrease
--------- -------------- -----------------
lisi 55 5
你max查的是最大的数,你取消删除了,但是你这个id我估计是自动是不会自动-1的所有你出现了你的数据对不上号的问题,你的id是不是设了自增长的?你应该用sum函数统计的
PKID int identity(1,1) not null,
[AccountID] [varchar](50) NOT NULL, --微博账号
[FollowersCount] [decimal](18, 0) NOT NULL, --总关注数
[CreateOn] [datetime] NOT NULL
)第二,SQL仅供参考,期待更优化的写法
select tMax.AccountID,tMax.FollowersCount - tMin.FollowersCount as NewFollowers,
tMax.FollowersCount as FollowersSum
from
(select PKID,AccountID,FollowersCount from WeiboStaticits where PKID in(select max(PKID) as PKID from WeiboStaticits
where CreateOn < GETDATE() and CreateOn > '2013-12-15'
group by AccountID)) tMax
inner join
(select PKID,AccountID,FollowersCount from WeiboStaticits where PKID in (select min(PKID) as PKID from WeiboStaticits
where CreateOn < GETDATE() and CreateOn > '2013-12-15'
group by AccountID)) tMin
on tMax.AccountID=tMin.AccountID