求一条SQL语句

[csdnFUCKINGSUCKS]

表结构如下

CREATE TABLE WeiboStaticits(
	[AccountID] [varchar](50) NOT NULL,          --微博账号
	[FollowersCount] [decimal](18, 0) NOT NULL,  --总关注数
	[CreateOn] [datetime] NOT NULL               
)

测试数据

AccountID  FollowersCount       CreateOn
---------  --------------       ---------
zhangsan   10                   2013-12-12 00:00:00.000
zhangsan   25                   2013-12-13 00:00:00.000
zhangsan   22                   2013-12-25 00:00:00.000
lisi       50                   2013-11-20 00:00:00.000
lisi       55                   2013-11-25 00:00:00.000

需求:
传入一个时间作为参数 查询到该时间内的 每个账号对应的总关注数及新增关注数
之前走入个误区 以为关注数只增不减的 语句写出来了 但验证之后有问题(因为微博可以取消关注)
特来求助 望大神指点迷津

[铁歌]

1 该时间内的 每个账号对应的总关注数

DECLARE @DATE DATE
SELECT AccountID, SUM(FollowersCount) FROM WeiboStaticits
WHERE CREATEON > @DATE
GROUP   BY AccountID

2.该时间内的 新增关注数 --要计算新增 关键是要有一个对比日期，假设计算某日和其一周前则可以如下统计：

DECLARE @DATE DATE
SELECT AccountID,SUM(FollowersCount) FROM WeiboStaticits
WHERE CREATEON <@DATE AND  CREATEON > @DATE -7
GROUP   BY AccountID

[csdnFUCKINGSUCKS]

引用 1 楼 ejason 的回复:

1 该时间内的 每个账号对应的总关注数

DECLARE @DATE DATE
SELECT AccountID, SUM(FollowersCount) FROM WeiboStaticits
WHERE CREATEON > @DATE
GROUP   BY AccountID

2.该时间内的 新增关注数 --要计算新增 关键是要有一个对比日期，假设计算某日和其一周前则可以如下统计：

DECLARE @DATE DATE
SELECT AccountID,SUM(FollowersCount) FROM WeiboStaticits
WHERE CREATEON <@DATE AND  CREATEON > @DATE -7
GROUP   BY AccountID

不需要count 因为FollowersCount的数据就是统计好的人数 举个例子 参数日期: 2013-12-31 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

[choueric]

一时想到这么个办法，结果似乎是符合要求的，方法比较笨，等待大神的回复，继续学习。。

CREATE TABLE WeiboStaticits(
    [AccountID] [varchar](50) NOT NULL,          --微博账号
    [FollowersCount] [decimal](18, 0) NOT NULL,  --总关注数
    [CreateOn] [datetime] NOT NULL               
)

insert into WeiboStaticits([AccountID],[FollowersCount],[CreateOn]) values('zhangsan',10,'2013-12-12')
insert into WeiboStaticits([AccountID],[FollowersCount],[CreateOn]) values('zhangsan',25,'2013-12-13')
insert into WeiboStaticits([AccountID],[FollowersCount],[CreateOn]) values('zhangsan',22,'2013-12-25')
insert into WeiboStaticits([AccountID],[FollowersCount],[CreateOn]) values('lisi',50,'2013-11-20')
insert into WeiboStaticits([AccountID],[FollowersCount],[CreateOn]) values('lisi',55,'2013-11-25')
------------------------------------------------------------------------------------------------
select AccountID,BFollowersCount FollowersSum,BFollowersCount - AFollowersCount FollowersIncrease from
(
	select  A.AccountID ,
			case when B.FollowersCount is null then 0 else A.FollowersCount end as AFollowersCount,
			A.CreateOn as ACreateOn,
			case when B.FollowersCount is null then A.FollowersCount else  B.FollowersCount end BFollowersCount,
			B.CreateOn as BCreate from 
	(
		select * from 
		(
		select ROW_NUMBER() over (partition by AccountID order by createon desc ) rowNum,* from WeiboStaticits 
		) as a
		where a.CreateOn <= '2013-12-31' and a.rowNum = 2
	) as A
	left join
	(
		select * from 
		(
		select ROW_NUMBER() over (partition by AccountID order by createon desc ) rowNum,* from WeiboStaticits 
		) as a
		where a.CreateOn <= '2013-12-31' and a.rowNum = 1
	) as B on A.AccountID = B.AccountID
) TempView

[發糞塗牆]

----------------------------------------------------------------
-- Author  :DBA_Huangzj(發糞塗牆)
-- Date    :2014-01-14 08:08:05
-- Version:
--      Microsoft SQL Server 2012 (SP1) - 11.0.3128.0 (X64) 
--	Dec 28 2012 20:23:12 
--	Copyright (c) Microsoft Corporation
--	Enterprise Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: )
--
----------------------------------------------------------------
--> 测试数据：[WeiboStaticits]
if object_id('[WeiboStaticits]') is not null drop table [WeiboStaticits]
go 
create table [WeiboStaticits]([AccountID] varchar(8),[FollowersCount] int,[CreateOn] datetime)
insert [WeiboStaticits]
select 'zhangsan',10,'2013-12-12 00:00:00.000' union all
select 'zhangsan',25,'2013-12-13 00:00:00.000' union all
select 'zhangsan',22,'2013-12-25 00:00:00.000' union all
select 'lisi',50,'2013-11-20 00:00:00.000' union all
select 'lisi',55,'2013-11-25 00:00:00.000'
--------------开始查询--------------------------
DECLARE @a DATETIME
SET @a='2013-12-25'
;WITH cte AS (
select * ,ROW_NUMBER()OVER (PARTITION BY [AccountID] ORDER BY [CreateOn] )oid
from [WeiboStaticits]),
cte2 AS (
SELECT *,0 newadd
FROM cte 
WHERE [CreateOn]<=@a AND oid=1
UNION ALL 
SELECT a.accountid,a.FollowersCount,a.[CreateOn],a.oid, a.FollowersCount-b.FollowersCount AS newadd 
FROM cte a INNER JOIN cte2 b ON a.oid=b.oid+1 AND a.accountid=b.accountid
--
)
SELECT accountid,FollowersCount,CreateOn,newadd [新增]
FROM cte2 a
WHERE EXISTS(SELECT 1 FROM (SELECT accountid,MAX(oid)oid FROM cte2 
WHERE  CreateOn<=@a
GROUP BY accountid)b WHERE a.accountid=b.accountid
AND a.oid=b.oid   )
ORDER BY accountid
 

----------------结果----------------------------
/* 
accountid FollowersCount CreateOn                新增
--------- -------------- ----------------------- -----------
lisi      55             2013-11-25 00:00:00.000 5
zhangsan  22             2013-12-25 00:00:00.000 -3
*/