已知5、6、7三个月的累计数 (累计数指的是当年1月份到当月的累计数,第一行指的是A单位当年1-5月份的累计数为55)单位 累计数 日期
A 55 2010-5-1
B 130 2010-5-1
C 220 2010-5-1
A 100 2010-6-1
B 200 2010-6-1
C 300 2010-6-1
A 163 2010-7-1
C 350 2010-7-1
D 20 2010-7-1得到7月份的结果:单位 上月数 本月数 累计数
A 45 63 163
C 80 50 350
D 0 20 20
if object_id('tempdb.dbo.#A') is not null drop table #A
create table #A (单位 varchar(1),累计数 int,日期 datetime)
insert into #A
select 'A',55,'2010-5-1' union all
select 'B',130,'2010-5-1' union all
select 'C',220,'2010-5-1' union all
select 'A',100,'2010-6-1' union all
select 'B',200,'2010-6-1' union all
select 'C',300,'2010-6-1' union all
select 'A',163,'2010-7-1' union all
select 'C',350,'2010-7-1' union all
select 'D',20,'2010-7-1'select
CONVERT(CHAR(7),日期,120) AS 期间,单位,累计数,
累计数-ISNULL((SELECT TOP 1 累计数 FROM #A WHERE 单位=T.单位 AND 日期<T.日期 ORDER BY 日期 DESC),0) AS 本月 INTO #T
from #A T--SELECT * FROM #TSELECT 期间,单位,ISNULL((SELECT TOP 1 本月 FROM #T WHERE 单位=T.单位 AND 期间<T.期间 ORDER BY 期间 DESC),0) AS 上月,本月,累计数 FROM #T T /*期间 单位 上月 本月 累计数
------- ---- ----------- ----------- -----------
2010-05 A 0 55 55
2010-05 B 0 130 130
2010-05 C 0 220 220
2010-06 A 55 45 100
2010-06 B 130 70 200
2010-06 C 220 80 300
2010-07 A 45 63 163
2010-07 C 80 50 350
2010-07 D 0 20 20(所影响的行数为 9 行)也帮楼主写个,楼主测试一下
isnull(a.累计数,0)-isnull(b.累计数,0) as 本月数,a.累计数
from (select * from tablename where month(日期)=@month) a
left join (select * from tablename where month(日期)=month(dateadd(mm,-1,日期))) b on a.单位=b.单位
left join (select * from tablename where month(日期)=month(dateadd(mm,-2,日期))) c on a.单位=c.单位
set @month=7select a.单位,isnull(b.累计数,0)-isnull(c.累计数,0) as 上月数,
isnull(a.累计数,0)-isnull(b.累计数,0) as 本月数,a.累计数
from (select * from #tablename where month(日期)=@month) a
left join (select * from #tablename where month(日期)=@month-1) b on a.单位=b.单位
left join (select * from #tablename where month(日期)=@month-2) c on a.单位=c.单位单位 上月数 本月数 累计数
A 45 63 163
C 80 50 350
D 0 20 20
declare @month int
set @month=7select a.单位,isnull(b.累计数,0)-isnull(c.累计数,0) as 上月数,
isnull(a.累计数,0)-isnull(b.累计数,0) as 本月数,a.累计数
from (select * from #tablename where month(日期)=@month) a
left join (select * from #tablename where month(日期)=case when @month=1 then 12 else @month-1 end) b on a.单位=b.单位
left join (select * from #tablename where month(日期)=case when @month=1 then 12 else @month-2 end) c on a.单位=c.单位单位 上月数 本月数 累计数
---- ----------- ----------- -----------
A 45 63 163
C 80 50 350
D 0 20 20
go
create table #t
(
unit varchar(10),
total int,
totalDate datetime
)
go
insert into #t values('A',55,'2010-5-1')
insert into #t values('B',130,'2010-5-1')
insert into #t values('C',220,'2010-5-1')
insert into #t values('A',100,'2010-6-1')
insert into #t values('B',200,'2010-6-1')
insert into #t values('C',300,'2010-6-1')
insert into #t values('A',163,'2010-7-1')
insert into #t values('C',350,'2010-7-1')
insert into #t values('D',20,'2010-7-1')
go
select unit,'上月总数=sum(case month(totalDate) when 6 then total else 0 end),'本月数'=sum(case month(totalDate) when 7 then total else 0 end),sum(total) as '累积数' from #t where month(totalDate)>5 group by unit
经测试能够得出正常的结果