多个case的用法.
原语句:
declare @sql varchar(8000),@sqlall varchar(8000)
set @sql=''
set @sqlall=''
select @sql=@sql+'sum(a.['+name+']),sum(b.['+name+']),
((sum(a.['+name+'])-sum(b.['+name+']))/sum(a.['+name+'])) as ['+name+'],'
from syscolumns
where id=object_id('t_costsepc_temp')
and name not in('zcname','lot','bh','w','wcname')
set @sql=left(@sql,len(@sql)-1)
set @sqlall='select a.zcname,a.w,a.wcname,' + @sql +'
FROM dbo.t_costsepc_temp a INNER JOIN
dbo.t_clnumber b ON a.zcname = b.zcname AND a.w = b.w AND
a.wcname = b.wcname INNER JOIN
dbo.t_blcostnumber c ON b.zcname = c.zcname AND b.w = c.w AND
b.wcname = c.wcname where a.zcname in(''制作'',''包裝'')
group by a.zcname,a.w,a.wcname
order by a.zcname '
exe (@sqlall)
这样的语句没有错,但因为会出现0,所以会出现
Divide by zero error encountered.
这种错误提示,
因为['+name+'])的名称最多会有31个,就是从01-31,这样一来就要使用多个case来判断,错误就产生了.
看看用上面的语句产生的全部整句sql:
select a.zcname,a.w,a.wcname,sum(a.[01]),sum(b.[01]),
((sum(a.[01])-sum(b.[01]))/sum(a.[01])) as [01],sum(a.[02]),sum(b.[02]),
((sum(a.[02])-sum(b.[02]))/sum(a.[02])) as [02],sum(a.[03]),sum(b.[03]),
((sum(a.[03])-sum(b.[03]))/sum(a.[03])) as [03],sum(a.[04]),sum(b.[04]),
((sum(a.[04])-sum(b.[04]))/sum(a.[04])) as [04],sum(a.[05]),sum(b.[05]),
((sum(a.[05])-sum(b.[05]))/sum(a.[05])) as [05],sum(a.[06]),sum(b.[06]),
((sum(a.[06])-sum(b.[06]))/sum(a.[06])) as [06],sum(a.[07]),sum(b.[07]),
((sum(a.[07])-sum(b.[07]))/sum(a.[07])) as [07],sum(a.[08]),sum(b.[08]),
((sum(a.[08])-sum(b.[08]))/sum(a.[08])) as [08],sum(a.[09]),sum(b.[09]),
((sum(a.[09])-sum(b.[09]))/sum(a.[09])) as [09],sum(a.[10]),sum(b.[10]),
((sum(a.[10])-sum(b.[10]))/sum(a.[10])) as [10],sum(a.[11]),sum(b.[11]),
((sum(a.[11])-sum(b.[11]))/sum(a.[11])) as [11],sum(a.[12]),sum(b.[12]),
((sum(a.[12])-sum(b.[12]))/sum(a.[12])) as [12],sum(a.[13]),sum(b.[13]),
((sum(a.[13])-sum(b.[13]))/sum(a.[13])) as [13],sum(a.[14]),sum(b.[14]),
((sum(a.[14])-sum(b.[14]))/sum(a.[14])) as [14],sum(a.[15]),sum(b.[15]),
((sum(a.[15])-sum(b.[15]))/sum(a.[15])) as [15],sum(a.[16]),sum(b.[16]),
((sum(a.[16])-sum(b.[16]))/sum(a.[16])) as [16],sum(a.[17]),sum(b.[17]),
((sum(a.[17])-sum(b.[17]))/sum(a.[17])) as [17],sum(a.[18]),sum(b.[18]),
((sum(a.[18])-sum(b.[18]))/sum(a.[18])) as [18],sum(a.[19]),sum(b.[19]),
((sum(a.[19])-sum(b.[19]))/sum(a.[19])) as [19],sum(a.[20]),sum(b.[20]),
((sum(a.[20])-sum(b.[20]))/sum(a.[20])) as [20],sum(a.[21]),sum(b.[21]),
((sum(a.[21])-sum(b.[21]))/sum(a.[21])) as [21],sum(a.[22]),sum(b.[22]),
((sum(a.[22])-sum(b.[22]))/sum(a.[22])) as [22],sum(a.[23]),sum(b.[23]),
((sum(a.[23])-sum(b.[23]))/sum(a.[23])) as [23],sum(a.[24]),sum(b.[24]),
((sum(a.[24])-sum(b.[24]))/sum(a.[24])) as [24],sum(a.[25]),sum(b.[25]),
((sum(a.[25])-sum(b.[25]))/sum(a.[25])) as [25],sum(a.[26]),sum(b.[26]),
((sum(a.[26])-sum(b.[26]))/sum(a.[26])) as [26],sum(a.[27]),sum(b.[27]),
((sum(a.[27])-sum(b.[27]))/sum(a.[27])) as [27],sum(a.[28]),sum(b.[28]),
((sum(a.[28])-sum(b.[28]))/sum(a.[28])) as [28],sum(a.[29]),sum(b.[29]),
((sum(a.[29])-sum(b.[29]))/sum(a.[29])) as [29],sum(a.[30]),sum(b.[30]),
((sum(a.[30])-sum(b.[30]))/sum(a.[30])) as [30],sum(a.[31]),sum(b.[31]),
((sum(a.[31])-sum(b.[31]))/sum(a.[31])) as [31],sum(a.[trnume]),sum(b.[trnume]),
((sum(a.[trnume])-sum(b.[trnume]))/sum(a.[trnume])) as [trnume]
FROM dbo.t_costsepc_temp a INNER JOIN
dbo.t_clnumber b ON a.zcname = b.zcname AND a.w = b.w AND
a.wcname = b.wcname INNER JOIN
dbo.t_blcostnumber c ON b.zcname = c.zcname AND b.w = c.w AND
b.wcname = c.wcname where a.zcname in('制作','包裝')
group by a.zcname,a.w,a.wcname
order by a.zcname
请大家看有没有其它的办法得到这个结果,因为t_costsepc_temp表的字段不是固定的...