if object_id('tempdb.dbo.#tb') is not null drop table #tb go create table #tb (dm varchar(8),value int,taxdate datetime) insert into #tb select '税1',6,'2010-09-01' union all select '税1',8,'2010-10-01' union all select '税2',3,'2010-10-01' union all select '罚没收入',9,'2010-10-01' union all select '附加税',2,'2010-10-01' union all select '罚没收入',1,'2010-09-01'select [项目内容]=isnull(a.dm,'小计'), [当月值]=sum(a.[value]-isnull(b.[value],0)), [累计值]=sum(a.[value]) from ( select dm=case when dm='税1' or dm='税2' then dm else '其它税' end, [value]=sum(value) from #tb where datediff(month,taxdate,'2010-10-01')=0 group by case when dm='税1' or dm='税2' then dm else '其它税' end )a left join ( select dm=case when dm='税1' or dm='税2' then dm else '其它税' end, [value]=sum(value) from #tb where datediff(month,taxdate,'2010-09-01')=0 group by case when dm='税1' or dm='税2' then dm else '其它税' end )b on a.dm=b.dm group by a.dm with rollup项目内容 当月值 累计值 -------- ----------- ----------- 其它税 10 11 税1 2 8 税2 3 3 小计 15 22
dm value taxdate
税1 6 2010-09-01
税1 8 2010-10-01
税2 3 2010-10-01
罚没收入 9 2010-10-01
附加税 2 2010-10-01
罚没收入 1 2010-09-01得到:2010年10月份的数据
项目内容 当月值 累计值
小计 15 22
税1 2 8
税2 3 3
其他税 10 11
go
create table #tb (dm varchar(8),value int,taxdate datetime)
insert into #tb
select '税1',6,'2010-09-01' union all
select '税1',8,'2010-10-01' union all
select '税2',3,'2010-10-01' union all
select '罚没收入',9,'2010-10-01' union all
select '附加税',2,'2010-10-01' union all
select '罚没收入',1,'2010-09-01'select [项目内容]=isnull(a.dm,'小计'),
[当月值]=sum(a.[value]-isnull(b.[value],0)),
[累计值]=sum(a.[value])
from
(
select dm=case when dm='税1' or dm='税2' then dm else '其它税' end,
[value]=sum(value)
from #tb where datediff(month,taxdate,'2010-10-01')=0
group by case when dm='税1' or dm='税2' then dm else '其它税' end
)a
left join
(
select dm=case when dm='税1' or dm='税2' then dm else '其它税' end,
[value]=sum(value)
from #tb where datediff(month,taxdate,'2010-09-01')=0
group by case when dm='税1' or dm='税2' then dm else '其它税' end
)b
on a.dm=b.dm
group by a.dm
with rollup项目内容 当月值 累计值
-------- ----------- -----------
其它税 10 11
税1 2 8
税2 3 3
小计 15 22