--创建示例表
Create Table T1
(_Date Varchar(10),RunTime INT)
Go
Insert T1 (_Date,RunTime)
Values ('2015.04',10),('2015.05',20),('2015.06',30),('2015.07',40),('2015.08',25),('2015.09',35),('2015.10',15),('2015.11',55)
Create Table T1
(_Date Varchar(10),RunTime INT)
Go
Insert T1 (_Date,RunTime)
Values ('2015.04',10),('2015.05',20),('2015.06',30),('2015.07',40),('2015.08',25),('2015.09',35),('2015.10',15),('2015.11',55)
--注意在字符串中,月份以 04、05表示,否则会影响后续的计算顺序,比如字符串 '2015.4'>'2015.11',但实际上4月要小于11月
Go
--以视图方式实现,以方便下游当成表来提取数据
Create View dbo.vT1
As
With C As
(Select _Date,LEFT(_Date,4) as YY,(Cast(Right(_Date,2) as Int)-1)/3+1 as Qt,RunTime,
ROW_NUMBER() OVER (ORDER BY _Date) as RN
From dbo.T1)
Select _Date,RunTime,
(Select Sum(RunTime) as A From C as C2 Where YY=C.YY and Qt=C.Qt and RN<=C.RN) as A,
(Select Sum(RunTime) as A From C as C3 Where RN<=C.RN) as B
From C
Go
Select * From dbo.vT1 Order By _Date
GO
Create View dbo.vT1
As
With C As
(Select _Date,LEFT(_Date,4) as YY,(Cast(Right(_Date,2) as Int)-1)/3+1 as Qt,RunTime,
ROW_NUMBER() OVER (ORDER BY _Date) as RN
From dbo.T1)
Select _Date,RunTime,
(Select Sum(RunTime) as A From C as C2 Where YY=C.YY and Qt=C.Qt and RN<=C.RN) as A,
(Select Sum(RunTime) as A From C as C3 Where RN<=C.RN) as B
From C
Go
Select * From dbo.vT1 Order By _Date
GO
--以SP实现,像报表一样
Create Procedure RepData
As
Declare @A INT=0,@B INT=0,@Qt INT=0
Select _Date,(Cast(Right(_Date,2) as Int)-1)/3+1 as Qt, --季度值
RunTime,@A as A,@B as B INTO #T
From T1
--建聚集索引是为了排序
Create Clustered Index CX_T_99999 On #T (_Date)
--逐行更新累计值
Update #T
Set @A=Case When @Qt=Qt Then @A+RunTime Else RunTime End,
@B=@B+RunTime,
A=@A,B=@B,@Qt=Qt
Select _Date,RunTime,A,B
From #T Order By _Date
Go
--执行:
Exec RepData
Create Procedure RepData
As
Declare @A INT=0,@B INT=0,@Qt INT=0
Select _Date,(Cast(Right(_Date,2) as Int)-1)/3+1 as Qt, --季度值
RunTime,@A as A,@B as B INTO #T
From T1
--建聚集索引是为了排序
Create Clustered Index CX_T_99999 On #T (_Date)
--逐行更新累计值
Update #T
Set @A=Case When @Qt=Qt Then @A+RunTime Else RunTime End,
@B=@B+RunTime,
A=@A,B=@B,@Qt=Qt
Select _Date,RunTime,A,B
From #T Order By _Date
Go
--执行:
Exec RepData
SQL Server 2008及以上版本支持,2005/2000得再调整下