我需要把月份那里根据传过来的@StartM 开始月份，@EndM结束月份
循环这两句话
'sum(case b.月份 when '+'1'+' then b.入库数量 else 0 end) AS'''+'一月入库数量'+''','+
'sum(case b.月份 when '+'1'+'then b.销售数量 else 0 end) AS '''+'一月销售数量'+''','
进行显示，而不是把12个月全写出来。。。。
alter proc UFSD_analyzestatistics(@Brand nvarchar(50),@Syear int ,@StartM int ,@EndM int)
as
DECLARE @sql nvarchar(max)
declare @TSyear nvarchar(50)
declare @TStartM nvarchar(50)
declare @TEndM nvarchar(50)
set @TSyear = cast(@Syear as nvarchar)
set @TStartM = cast(@StartM as nvarchar )
set @TEndM = cast( @EndM as nvarchar)
if object_id('Analyzestatistics') is not null
drop table Analyzestatistics
BEGIN
set @sql='select * into Analyzestatistics from
(select b.产品规格,b.产品编号,'
if ( @StartM =1 or @StartM=@EndM or @EndM >=1)
set @sql=@sql+
'sum(case b.月份 when '+'1'+' then b.入库数量 else 0 end) AS'''+'一月入库数量'+''','+
'sum(case b.月份 when '+'1'+'then b.销售数量 else 0 end) AS '''+'一月销售数量'+''','
if( @StartM =2 or @StartM=@EndM or @EndM >=2)
set @sql=@sql+
'sum(case b.月份 when '+'2'+' then b.入库数量 else 0 end) AS '''+'二月入库数量'+''','+
'sum(case b.月份 when '+'2'+' then b.销售数量 else 0 end) AS '''+'二月销售数量'+''','
if (@StartM =3 or @StartM=@EndM or @EndM >=3)
set @sql=@sql+
'sum(case b.月份 when '+'3'+' then b.入库数量 else 0 end) AS'''+'三月入库数量'+''','+
'sum(case b.月份 when '+'3'+' then b.销售数量 else 0 end) AS '''+'三月销售数量'+''','
if( @StartM =4 or @StartM=@EndM or @EndM >=4)
set @sql=@sql+
'sum(case b.月份 when '+'4'+' then b.入库数量 else 0 end) AS '''+'四月入库数量'+''','+
'sum(case b.月份 when '+'4'+' then b.销售数量 else 0 end) AS '''+'四月销售数量'+''','
if( @StartM =5 or @StartM=@EndM or @EndM >=5)
set @sql=@sql+
'sum(case b.月份 when '+'5'+' then b.入库数量 else 0 end) AS '''+'五月入库数量'+''','+
'sum(case b.月份 when '+'5'+' then b.销售数量 else 0 end) AS '''+'五月销售数量'+''','
if( @StartM =6 or @StartM=@EndM or @EndM >=6)
set @sql=@sql+
'sum(case b.月份 when '+'6'+' then b.入库数量 else 0 end) AS '''+'六月入库数量'+''','+
'sum(case b.月份 when '+'6'+' then b.销售数量 else 0 end) AS '''+'六月销售数量'+''','
if( @StartM =7 or @StartM=@EndM or @EndM >=7)
set @sql=@sql+
'sum(case b.月份 when '+'7'+' then b.入库数量 else 0 end) AS '+'七月入库数量'+''','+
'sum(case b.月份 when '+'7'+' then b.销售数量 else 0 end) AS'' '+'七月销售数量'+''','
if( @StartM =8 or @StartM=@EndM or @EndM >=8)
set @sql=@sql+
'sum(case b.月份 when '+'8'+' then b.入库数量 else 0 end) AS '''+'八月入库数量'+''','+
'sum(case b.月份 when '+'8'+' then b.销售数量 else 0 end) AS '''+'八月销售数量'+''','
if( @StartM =9 or @StartM=@EndM or @EndM >=9)
set @sql=@sql+
'sum(case b.月份 when '+'9'+' then b.入库数量 else 0 end) AS '''+'九月入库数量'+''','+
'sum(case b.月份 when '+'9'+' then b.销售数量 else 0 end) AS '''+'九月销售数量'+''','
if( @StartM =10 or @StartM=@EndM or @EndM >=10)
set @sql=@sql+
'sum(case b.月份 when '+'10'+' then b.入库数量 else 0 end) AS '''+'十月入库数量'+''','+
'sum(case b.月份 when '+'10'+'then b.入库数量 else 0 end) AS '''+'十月入库数量'+''','
if( @StartM =11 or @StartM=@EndM or @EndM >=11)
set @sql=@sql+
'sum(case b.月份 when '+'11'+' then b.销售数量 else 0 end) AS '''+'十一月销售数量'+''','+
'sum(case b.月份 when '+'11'+' then b.入库数量 else 0 end) AS'+ '''十一月入库数量'+''','
if( @StartM =12 or @StartM=@EndM or @EndM >=12)
set @sql=@sql+
'sum(case b.月份 when '+'12'+' then b.销售数量 else 0 end) AS '''+'十二月销售数量'+''','+
'sum(case b.月份 when '+'12'+' then b.入库数量 else 0 end) AS '''+'十二月入库数量'+''','
set @sql=@sql+ 'b.总计入库数量,
b.总计销售数量
from(select 产品规格=it.cInvStd ,
产品编号=dls.cInvCode ,
月份=month(dl.dDate),
入库数量=sum(rcs10.iQuantity) ,
销售数量=sum(dls.iQuantity),
总计入库数量=(select sum(rcses10.iQuantity )
from rdrecords10 rcses10 with(nolock),rdrecord10 rcss10 with(nolock)
where rcses10.cInvCode=dls.cInvCode
and rcses10.ID=rcss10.ID
and YEAR(rcss10.dDate)='''+@TSyear+ '''and month(rcss10.dDate) between'''+ @TStartM +'''and'''+ @TEndM +'''),
总计销售数量=(select sum(dlses.iQuantity )
from DispatchList dlss,DispatchLists dlses
where dlses.cInvCode=dls.cInvCode
and dlss.DLID=dlses.DLID
and YEAR(dlss.dDate)='''+@TSyear+'''and month(dlss.dDate) between'''+ @TStartM +'''and'''+ @TEndM+''')
from DispatchLists dls with(nolock),DispatchList dl with(nolock),Inventory it with(nolock),
rdrecords10 rcs10 with(nolock),rdrecord10 rc10 with(nolock),Inventory iv with(nolock)
where dls.DLID=dl.DLID
and rcs10.ID=rc10.ID
and bReturnFlag=0
and dls.cInvCode=it.cInvCode
and rcs10.cInvCode=it.cInvCode
and iv.cInvCode=it.cInvCode
and iv.cInvDefine2='''+@Brand+
'''and year(dl.dDate)='''+@TSyear +'''and month(dl.dDate) between '''+@TStartM +'''and'''+ @TEndM+
'''and year(rc10.dDate)='''+@TSyear+ '''and month(rc10.dDate) between'''+@TStartM +'''and'''+ @TEndM+
'''group by it.cInvStd,dls.cInvCode,month(dl.dDate)
union all
select a.产品规格,a.小计,a.月份,
入库数量=SUM(a.入库数量),
销售数量=SUM(a.销售数量),
入库数量总计=(select SUM(rcses10.iQuantity)
from Inventory its with(nolock),rdrecords10 rcses10 with(nolock)
where its.cInvCode=rcses10.cInvCode
and its.cInvStd=a.产品规格
group by cInvStd),
销售数量总计=(select SUM(dlses.iQuantity)
from Inventory its with(nolock),DispatchLists dlses with(nolock)
where its.cInvCode=dlses.cInvCode
and its.cInvStd=a.产品规格
group by cInvStd)
from (select 产品规格=it.cInvStd,小计='''+'小计'+''',
月份=month(dl.dDate),
入库数量=SUM(rcs10.iQuantity),
销售数量=SUM(dls.iQuantity)
from Inventory it with(nolock),rdrecords10 rcs10 with(nolock),rdrecord10 rc10 with(nolock),
DispatchLists dls with(nolock),DispatchList dl with(nolock),Inventory iv with(nolock)
where dls.DLID=dl.DLID
and rcs10.ID=rc10.ID
and it.cInvCode=dls.cInvCode
and it.cInvCode=rcs10.cInvCode
and bReturnFlag=0
and iv.cInvCode=it.cInvCode
and iv.cInvDefine2='''+@Brand+
'''and year(dl.dDate)='''+@TSyear+'''and month(dl.dDate) between'''+@TStartM +'''and'''+ @TEndM+
'''and year(rc10.dDate)='''+@TSyear +'''and month(rc10.dDate) between'''+ @TStartM +'''and'''+ @TEndM+
'''group by it.cInvStd,month(dl.dDate),it.cInvCode) a
group by a.产品规格,a.小计,a.月份) b
group by b.产品规格,b.产品编号,b.总计入库数量,b.总计销售数量 ) old'
exec ( @sql)
--print @sql
end
------解决思路----------------------
太复杂了吧，帮顶了，
------解决思路----------------------
可以跨年，比如9月～2月。
其它部分自己拼
DECLARE @M int
DECLARE @Mnum nvarchar(11)
DECLARE @Mcn nvarchar(2)
set @sql='...'
SET @M = @StartM
WHILE 1=1
BEGIN
SET @Mnum = Convert(nvarchar(11), @M)
SET @Mcn = CASE @M WHEN 11 THEN '十一'
WHEN 12 THEN '十二'
ELSE SUBSTRING('一二三四五六七八九十',@M,1)
END
SET @sql=@sql+
'sum(case b.月份 when '+@Mnum+' then b.入库数量 else 0 end) AS '''+@Mcn+'月入库数量'+''','+
'sum(case b.月份 when '+@Mnum+' then b.销售数量 else 0 end) AS '''+@Mcn+'月销售数量'+''','
IF @M = @EndM
BREAK
ELSE IF @M = 12
SET @M = 1
ELSE
SET @M = @M + 1
END