select xmdm as 项目,
sum(case when jhbh = '2003' then jhcl else 0 end) as 年计划,
sum(case when jhbh = '200304' then jhcl else 0 end) as 4月份计划,
sum(case when jhbh between '200301' and '200304' then jhcl else 0 end) 1_4月累计,
sum(case when jhbh = '200305' then jhcl else 0 end) as 5月份计划,
from 表
group by XMDM
sum(case when jhbh = '2003' then jhcl else 0 end) as 年计划,
sum(case when jhbh = '200304' then jhcl else 0 end) as 4月份计划,
sum(case when jhbh between '200301' and '200304' then jhcl else 0 end) 1_4月累计,
sum(case when jhbh = '200305' then jhcl else 0 end) as 5月份计划,
from 表
group by XMDM
case when jhbh='2003' then JHCL end 4月份计划,
case when jhbh>='200301' and jhbh<='200304' then JHCL end 4月份计划,..
from table group by XMDM
sum(case when jhbh='2003' then jhcl else 0 end) [年计划],
sum(case when jhbh='200304' then jhcl else 0 end) [4月份计划],
sum(case when jhbh between '200301' and '200304' then jhcl else 0 end) [1_4月累计],
sum(case when jhbh='200305' then jhcl else 0 end) [5月份计划]
from 表
group by XMDM但只可以做成报表,不可以直接录入保存,你必须自己写代码保存
select xmdm 项目,
sum(case when jhbh='2003' then jhcl else 0 end) [年计划],
sum(case when jhbh='200304' then jhcl else 0 end) [4月份计划],
sum(case when jhbh between '200301' and '200304' then jhcl else 0 end) [1_4月累计],
sum(case when jhbh='200305' then jhcl else 0 end) [5月份计划]
from 表
group by XMDM--输入当月数据进行保存的话,要在程序中写代码来实现.
@year char(4), --要统计的年份
@month char(6), --统计的月份+1,即计划月份
as
set nocount on
declare @m1 char(6),@m2 char(6),@m varchar(2),@sql varchar(8000)select @m1=@year+'01'
,@m2=convert(varchar(6),dateadd(month,-1,@m+'01'),112)
,@m=cast(month(@m2+'01') as varchar)
,@sql='select 项目=xmdm,
年计划=sum(case when jhbh='''+@year+''' then jhcl else 0 end),
['+@m+'月计划]=sum(case when jhbh='''+@m2+''' then jhcl else 0 end),
[1-'+@m+'月累计]=sum(case when jhbh between '''+@m1+''' and '''+@m2+''' then jhcl else 0 end),
['+cast(cast(@m as int)+1 as varchar)+'月份计划]=sum(case when jhbh='''+@month+''' then jhcl else 0 end) from 表
group by XMDM'
exec(@sql)
go