多个case的用法.

Question

原语句:
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表的字段不是固定的...

Answer 1

因为要判断 (sum(a.['+name+'])-sum(b.['+name+']))是否会等于0,所以只能用case，但要怎样使用呢?试了很久,不得其解...请大侠帮助!谢谢!

Answer 2

还有就是要担心加入case后字符超过8000的问题...

Answer 3

这样试试:(注意大写的CASE WHEN)
declare @sql varchar(8000),@sqlall varchar(8000)
set @sql=''
set @sqlall=''
select @sql=@sql+'sum(a.['+name+']),sum(b.['+name+']),
           (CASE SUM(a.[' + name + ']) WHEN 0 THEN 0 ELSE (sum(a.['+name+'])-sum(b.['+name+']))/sum(a.['+name+'])) as ['+name+'] END,'
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)

Answer 4

感谢楼上...因为要判断 sum(a.['+name+'])-sum(b.['+name+'])是为为0,所以我加入了-SUM(b.[' + name + ']) ,结果全部语句就是这样:
select a.zcname,a.w,a.wcname,sum(a.[01]),sum(b.[01]),
           (CASE SUM(a.[01])-SUM(b.[01]) WHEN 0 THEN 0 ELSE (sum(a.[01])-sum(b.[01]))/sum(a.[01])) as [01] END,sum(a.[02]),sum(b.[02]),
           (CASE SUM(a.[02])-SUM(b.[02]) WHEN 0 THEN 0 ELSE (sum(a.[02])-sum(b.[02]))/sum(a.[02])) as [02] END,sum(a.[03]),sum(b.[03]),
           (CASE SUM(a.[03])-SUM(b.[03]) WHEN 0 THEN 0 ELSE (sum(a.[03])-sum(b.[03]))/sum(a.[03])) as [03] END,sum(a.[04]),sum(b.[04]),
           (CASE SUM(a.[04])-SUM(b.[04]) WHEN 0 THEN 0 ELSE (sum(a.[04])-sum(b.[04]))/sum(a.[04])) as [04] END,sum(a.[05]),sum(b.[05]),
           (CASE SUM(a.[05])-SUM(b.[05]) WHEN 0 THEN 0 ELSE (sum(a.[05])-sum(b.[05]))/sum(a.[05])) as [05] END,sum(a.[06]),sum(b.[06]),
           (CASE SUM(a.[06])-SUM(b.[06]) WHEN 0 THEN 0 ELSE (sum(a.[06])-sum(b.[06]))/sum(a.[06])) as [06] END,sum(a.[07]),sum(b.[07]),
           (CASE SUM(a.[07])-SUM(b.[07]) WHEN 0 THEN 0 ELSE (sum(a.[07])-sum(b.[07]))/sum(a.[07])) as [07] END,sum(a.[08]),sum(b.[08]),
           (CASE SUM(a.[08])-SUM(b.[08]) WHEN 0 THEN 0 ELSE (sum(a.[08])-sum(b.[08]))/sum(a.[08])) as [08] END,sum(a.[09]),sum(b.[09]),
           (CASE SUM(a.[09])-SUM(b.[09]) WHEN 0 THEN 0 ELSE (sum(a.[09])-sum(b.[09]))/sum(a.[09])) as [09] END,sum(a.[10]),sum(b.[10]),
           (CASE SUM(a.[10])-SUM(b.[10]) WHEN 0 THEN 0 ELSE (sum(a.[10])-sum(b.[10]))/sum(a.[10])) as [10] END,sum(a.[11]),sum(b.[11]),
           (CASE SUM(a.[11])-SUM(b.[11]) WHEN 0 THEN 0 ELSE (sum(a.[11])-sum(b.[11]))/sum(a.[11])) as [11] END,sum(a.[12]),sum(b.[12]),
           (CASE SUM(a.[12])-SUM(b.[12]) WHEN 0 THEN 0 ELSE (sum(a.[12])-sum(b.[12]))/sum(a.[12])) as [12] END,sum(a.[13]),sum(b.[13]),
           (CASE SUM(a.[13])-SUM(b.[13]) WHEN 0 THEN 0 ELSE (sum(a.[13])-sum(b.[13]))/sum(a.[13])) as [13] END,sum(a.[14]),sum(b.[14]),
           (CASE SUM(a.[14])-SUM(b.[14]) WHEN 0 THEN 0 ELSE (sum(a.[14])-sum(b.[14]))/sum(a.[14])) as [14] END,sum(a.[15]),sum(b.[15]),
           (CASE SUM(a.[15])-SUM(b.[15]) WHEN 0 THEN 0 ELSE (sum(a.[15])-sum(b.[15]))/sum(a.[15])) as [15] END,sum(a.[16]),sum(b.[16]),
           (CASE SUM(a.[16])-SUM(b.[16]) WHEN 0 THEN 0 ELSE (sum(a.[16])-sum(b.[16]))/sum(a.[16])) as [16] END,sum(a.[17]),sum(b.[17]),
           (CASE SUM(a.[17])-SUM(b.[17]) WHEN 0 THEN 0 ELSE (sum(a.[17])-sum(b.[17]))/sum(a.[17])) as [17] END,sum(a.[18]),sum(b.[18]),
           (CASE SUM(a.[18])-SUM(b.[18]) WHEN 0 THEN 0 ELSE (sum(a.[18])-sum(b.[18]))/sum(a.[18])) as [18] END,sum(a.[19]),sum(b.[19]),
           (CASE SUM(a.[19])-SUM(b.[19]) WHEN 0 THEN 0 ELSE (sum(a.[19])-sum(b.[19]))/sum(a.[19])) as [19] END,sum(a.[20]),sum(b.[20]),
           (CASE SUM(a.[20])-SUM(b.[20]) WHEN 0 THEN 0 ELSE (sum(a.[20])-sum(b.[20]))/sum(a.[20])) as [20] END,sum(a.[21]),sum(b.[21]),
           (CASE SUM(a.[21])-SUM(b.[21]) WHEN 0 THEN 0 ELSE (sum(a.[21])-sum(b.[21]))/sum(a.[21])) as [21] END,sum(a.[22]),sum(b.[22]),
           (CASE SUM(a.[22])-SUM(b.[22]) WHEN 0 THEN 0 ELSE (sum(a.[22])-sum(b.[22]))/sum(a.[22])) as [22] END,sum(a.[23]),sum(b.[23]),
           (CASE SUM(a.[23])-SUM(b.[23]) WHEN 0 THEN 0 ELSE (sum(a.[23])-sum(b.[23]))/sum(a.[23])) as [23] END,sum(a.[24]),sum(b.[24]),
           (CASE SUM(a.[24])-SUM(b.[24]) WHEN 0 THEN 0 ELSE (sum(a.[24])-sum(b.[24]))/sum(a.[24])) as [24] END,sum(a.[25]),sum(b.[25]),
           (CASE SUM(a.[25])-SUM(b.[25]) WHEN 0 THEN 0 ELSE (sum(a.[25])-sum(b.[25]))/sum(a.[25])) as [25] END,sum(a.[26]),sum(b.[26]),
           (CASE SUM(a.[26])-SUM(b.[26]) WHEN 0 THEN 0 ELSE (sum(a.[26])-sum(b.[26]))/sum(a.[26])) as [26] END,sum(a.[27]),sum(b.[27]),
           (CASE SUM(a.[27])-SUM(b.[27]) WHEN 0 THEN 0 ELSE (sum(a.[27])-sum(b.[27]))/sum(a.[27])) as [27] END,sum(a.[28]),sum(b.[28]),
           (CASE SUM(a.[28])-SUM(b.[28]) WHEN 0 THEN 0 ELSE (sum(a.[28])-sum(b.[28]))/sum(a.[28])) as [28] END,sum(a.[29]),sum(b.[29]),
           (CASE SUM(a.[29])-SUM(b.[29]) WHEN 0 THEN 0 ELSE (sum(a.[29])-sum(b.[29]))/sum(a.[29])) as [29] END
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 暂且不理会字符的长度,返回的结果是:
Line 2: Incorrect syntax near ')'.

Answer 5

set @sqlall=''
select @sql=@sql+'sum(a.['+name+']),sum(b.['+name+']),
           ((sum(a.['+name+'])-sum(b.['+name+']))/case when sum(a.['+name+'])=0 then 1 else sum(a.['+name+']) end) 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)

Answer 6

嵌套的case when 语句就可以

Answer 7

--case when 的语法两种：--1、case when 变量=0 then 1 else 2 end as 别名
--2、case 变量 when 0 then 1 else 2 end as 别名select a.zcname,a.w,a.wcname,sum(a.[01]),sum(b.[01]),
           (CASE SUM(a.[01])-SUM(b.[01]) WHEN 0 THEN 0 ELSE (sum(a.[01])-sum(b.[01]))/sum(a.[01]) end) as [01], sum(a.[02]),sum(b.[02]),
           (CASE SUM(a.[02])-SUM(b.[02]) WHEN 0 THEN 0 ELSE (sum(a.[02])-sum(b.[02]))/sum(a.[02]) end) as [02], sum(a.[03]),sum(b.[03]),
           (CASE SUM(a.[03])-SUM(b.[03]) WHEN 0 THEN 0 ELSE (sum(a.[03])-sum(b.[03]))/sum(a.[03]) end) as [03], sum(a.[04]),sum(b.[04]),
           (CASE SUM(a.[04])-SUM(b.[04]) WHEN 0 THEN 0 ELSE (sum(a.[04])-sum(b.[04]))/sum(a.[04]) end) as [04], sum(a.[05]),sum(b.[05]),
           (CASE SUM(a.[05])-SUM(b.[05]) WHEN 0 THEN 0 ELSE (sum(a.[05])-sum(b.[05]))/sum(a.[05]) end) as [05], sum(a.[06]),sum(b.[06]),
           (CASE SUM(a.[06])-SUM(b.[06]) WHEN 0 THEN 0 ELSE (sum(a.[06])-sum(b.[06]))/sum(a.[06]) end) as [06], sum(a.[07]),sum(b.[07]),
           (CASE SUM(a.[07])-SUM(b.[07]) WHEN 0 THEN 0 ELSE (sum(a.[07])-sum(b.[07]))/sum(a.[07]) end) as [07], sum(a.[08]),sum(b.[08]),
           (CASE SUM(a.[08])-SUM(b.[08]) WHEN 0 THEN 0 ELSE (sum(a.[08])-sum(b.[08]))/sum(a.[08]) end) as [08], sum(a.[09]),sum(b.[09]),
           (CASE SUM(a.[09])-SUM(b.[09]) WHEN 0 THEN 0 ELSE (sum(a.[09])-sum(b.[09]))/sum(a.[09]) end) as [09], sum(a.[10]),sum(b.[10]),
           (CASE SUM(a.[10])-SUM(b.[10]) WHEN 0 THEN 0 ELSE (sum(a.[10])-sum(b.[10]))/sum(a.[10]) end) as [10], sum(a.[11]),sum(b.[11]),
           (CASE SUM(a.[11])-SUM(b.[11]) WHEN 0 THEN 0 ELSE (sum(a.[11])-sum(b.[11]))/sum(a.[11]) end) as [11], sum(a.[12]),sum(b.[12]),
           (CASE SUM(a.[12])-SUM(b.[12]) WHEN 0 THEN 0 ELSE (sum(a.[12])-sum(b.[12]))/sum(a.[12]) end) as [12], sum(a.[13]),sum(b.[13]),
           (CASE SUM(a.[13])-SUM(b.[13]) WHEN 0 THEN 0 ELSE (sum(a.[13])-sum(b.[13]))/sum(a.[13]) end) as [13], sum(a.[14]),sum(b.[14]),
           (CASE SUM(a.[14])-SUM(b.[14]) WHEN 0 THEN 0 ELSE (sum(a.[14])-sum(b.[14]))/sum(a.[14]) end) as [14], sum(a.[15]),sum(b.[15]),
           (CASE SUM(a.[15])-SUM(b.[15]) WHEN 0 THEN 0 ELSE (sum(a.[15])-sum(b.[15]))/sum(a.[15]) end) as [15], sum(a.[16]),sum(b.[16]),
           (CASE SUM(a.[16])-SUM(b.[16]) WHEN 0 THEN 0 ELSE (sum(a.[16])-sum(b.[16]))/sum(a.[16]) end) as [16], sum(a.[17]),sum(b.[17]),
           (CASE SUM(a.[17])-SUM(b.[17]) WHEN 0 THEN 0 ELSE (sum(a.[17])-sum(b.[17]))/sum(a.[17]) end) as [17], sum(a.[18]),sum(b.[18]),
           (CASE SUM(a.[18])-SUM(b.[18]) WHEN 0 THEN 0 ELSE (sum(a.[18])-sum(b.[18]))/sum(a.[18]) end) as [18], sum(a.[19]),sum(b.[19]),
           (CASE SUM(a.[19])-SUM(b.[19]) WHEN 0 THEN 0 ELSE (sum(a.[19])-sum(b.[19]))/sum(a.[19]) end) as [19], sum(a.[20]),sum(b.[20]),
           (CASE SUM(a.[20])-SUM(b.[20]) WHEN 0 THEN 0 ELSE (sum(a.[20])-sum(b.[20]))/sum(a.[20]) end) as [20], sum(a.[21]),sum(b.[21]),
           (CASE SUM(a.[21])-SUM(b.[21]) WHEN 0 THEN 0 ELSE (sum(a.[21])-sum(b.[21]))/sum(a.[21]) end) as [21], sum(a.[22]),sum(b.[22]),
           (CASE SUM(a.[22])-SUM(b.[22]) WHEN 0 THEN 0 ELSE (sum(a.[22])-sum(b.[22]))/sum(a.[22]) end) as [22], sum(a.[23]),sum(b.[23]),
           (CASE SUM(a.[23])-SUM(b.[23]) WHEN 0 THEN 0 ELSE (sum(a.[23])-sum(b.[23]))/sum(a.[23]) end) as [23], sum(a.[24]),sum(b.[24]),
           (CASE SUM(a.[24])-SUM(b.[24]) WHEN 0 THEN 0 ELSE (sum(a.[24])-sum(b.[24]))/sum(a.[24]) end) as [24], sum(a.[25]),sum(b.[25]),
           (CASE SUM(a.[25])-SUM(b.[25]) WHEN 0 THEN 0 ELSE (sum(a.[25])-sum(b.[25]))/sum(a.[25]) end) as [25], sum(a.[26]),sum(b.[26]),
           (CASE SUM(a.[26])-SUM(b.[26]) WHEN 0 THEN 0 ELSE (sum(a.[26])-sum(b.[26]))/sum(a.[26]) end) as [26], sum(a.[27]),sum(b.[27]),
           (CASE SUM(a.[27])-SUM(b.[27]) WHEN 0 THEN 0 ELSE (sum(a.[27])-sum(b.[27]))/sum(a.[27]) end) as [27], sum(a.[28]),sum(b.[28]),
           (CASE SUM(a.[28])-SUM(b.[28]) WHEN 0 THEN 0 ELSE (sum(a.[28])-sum(b.[28]))/sum(a.[28]) end) as [28], sum(a.[29]),sum(b.[29]),
           (CASE SUM(a.[29])-SUM(b.[29]) WHEN 0 THEN 0 ELSE (sum(a.[29])-sum(b.[29]))/sum(a.[29]) end) as [29]
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 

Answer 8

是...楼上的对,将 ) as ['+name+']放在end后面就可以运行...但就产生了另外一个问题...长度不够...如,@sql的值执行后结果为:
sum(a.[01]),sum(b.[01]),
           (CASE SUM(a.[01])-SUM(b.[01]) WHEN 0 THEN 0 ELSE (sum(a.[01])-sum(b.[01]))/sum(a.[01]) END) as [01],sum(a.[02]),sum(b.[02]),
           (CASE SUM(a.[02])-SUM(b.[02]) WHEN 0 THEN 0 ELSE (sum(a.[02])-sum(b.[02]))/sum(a.[02]) END) as [02],sum(a.[03]),sum(b.[03]),
           (CASE SUM(a.[03])-SUM(b.[03]) WHEN 0 THEN 0 ELSE (sum(a.[03])-sum(b.[03]))/sum(a.[03]) END) as [03],sum(a.[04]),sum(b.[04]),
           (CASE SUM(a.[04])-SUM(b.[04]) WHEN 0 THEN 0 ELSE (sum(a.[04])-sum(b.[04]))/sum(a.[04]) END) as [04],sum(a.[05]),sum(b.[05]),
           (CASE SUM(a.[05])-SUM(b.[05]) WHEN 0 THEN 0 ELSE (sum(a.[05])-sum(b.[05]))/sum(a.[05]) END) as [05],sum(a.[06]),sum(b.[06]),
           (CASE SUM(a.[06])-SUM(b.[06]) WHEN 0 THEN 0 ELSE (sum(a.[06])-sum(b.[06]))/sum(a.[06]) END) as [06],sum(a.[07]),sum(b.[07]),
           (CASE SUM(a.[07])-SUM(b.[07]) WHEN 0 THEN 0 ELSE (sum(a.[07])-sum(b.[07]))/sum(a.[07]) END) as [07],sum(a.[08]),sum(b.[08]),
           (CASE SUM(a.[08])-SUM(b.[08]) WHEN 0 THEN 0 ELSE (sum(a.[08])-sum(b.[08]))/sum(a.[08]) END) as [08],sum(a.[09]),sum(b.[09]),
           (CASE SUM(a.[09])-SUM(b.[09]) WHEN 0 THEN 0 ELSE (sum(a.[09])-sum(b.[09]))/sum(a.[09]) END) as [09],sum(a.[10]),sum(b.[10]),
           (CASE SUM(a.[10])-SUM(b.[10]) WHEN 0 THEN 0 ELSE (sum(a.[10])-sum(b.[10]))/sum(a.[10]) END) as [10],sum(a.[11]),sum(b.[11]),
           (CASE SUM(a.[11])-SUM(b.[11]) WHEN 0 THEN 0 ELSE (sum(a.[11])-sum(b.[11]))/sum(a.[11]) END) as [11],sum(a.[12]),sum(b.[12]),
           (CASE SUM(a.[12])-SUM(b.[12]) WHEN 0 THEN 0 ELSE (sum(a.[12])-sum(b.[12]))/sum(a.[12]) END) as [12],sum(a.[13]),sum(b.[13]),
           (CASE SUM(a.[13])-SUM(b.[13]) WHEN 0 THEN 0 ELSE (sum(a.[13])-sum(b.[13]))/sum(a.[13]) END) as [13],sum(a.[14]),sum(b.[14]),
           (CASE SUM(a.[14])-SUM(b.[14]) WHEN 0 THEN 0 ELSE (sum(a.[14])-sum(b.[14]))/sum(a.[14]) END) as [14],sum(a.[15]),sum(b.[15]),
           (CASE SUM(a.[15])-SUM(b.[15]) WHEN 0 THEN 0 ELSE (sum(a.[15])-sum(b.[15]))/sum(a.[15]) END) as [15],sum(a.[16]),sum(b.[16]),
           (CASE SUM(a.[16])-SUM(b.[16]) WHEN 0 THEN 0 ELSE (sum(a.[16])-sum(b.[16]))/sum(a.[16]) END) as [16],sum(a.[17]),sum(b.[17]),
           (CASE SUM(a.[17])-SUM(b.[17]) WHEN 0 THEN 0 ELSE (sum(a.[17])-sum(b.[17]))/sum(a.[17]) END) as [17],sum(a.[18]),sum(b.[18]),
           (CASE SUM(a.[18])-SUM(b.[18]) WHEN 0 THEN 0 ELSE (sum(a.[18])-sum(b.[18]))/sum(a.[18]) END) as [18],sum(a.[19]),sum(b.[19]),
           (CASE SUM(a.[19])-SUM(b.[19]) WHEN 0 THEN 0 ELSE (sum(a.[19])-sum(b.[19]))/sum(a.[19]) END) as [19],sum(a.[20]),sum(b.[20]),
           (CASE SUM(a.[20])-SUM(b.[20]) WHEN 0 THEN 0 ELSE (sum(a.[20])-sum(b.[20]))/sum(a.[20]) END) as [20],sum(a.[21]),sum(b.[21]),
           (CASE SUM(a.[21])-SUM(b.[21]) WHEN 0 THEN 0 ELSE (sum(a.[21])-sum(b.[21]))/sum(a.[21]) END) as [21],sum(a.[22]),sum(b.[22]),
           (CASE SUM(a.[22])-SUM(b.[22]) WHEN 0 THEN 0 ELSE (sum(a.[22])-sum(b.[22]))/sum(a.[22]) END) as [22],sum(a.[23]),sum(b.[23]),
           (CASE SUM(a.[23])-SUM(b.[23]) WHEN 0 THEN 0 ELSE (sum(a.[23])-sum(b.[23]))/sum(a.[23]) END) as [23],sum(a.[24]),sum(b.[24]),
           (CASE SUM(a.[24])-SUM(b.[24]) WHEN 0 THEN 0 ELSE (sum(a.[24])-sum(b.[24]))/sum(a.[24]) END) as [24],sum(a.[25]),sum(b.[25]),
           (CASE SUM(a.[25])-SUM(b.[25]) WHEN 0 THEN 0 ELSE (sum(a.[25])-sum(b.[25]))/sum(a.[25]) END) as [25],sum(a.[26]),sum(b.[26]),
           (CASE SUM(a.[26])-SUM(b.[26]) WHEN 0 THEN 0 ELSE (sum(a.[26])-sum(b.[26]))/sum(a.[26]) END) as [26],sum(a.[27]),sum(b.[27]),
           (CASE SUM(a.[27])-SUM(b.[27]) WHEN 0 THEN 0 ELSE (sum(a.[27])-sum(b.[27]))/sum(a.[27]) END) as [27],sum(a.[28]),sum(b.[28]),
           (CASE SUM(a.[28])-SUM(b.[28]) WHEN 0 THEN 0 ELSE (sum(a.[28])-sum(b.[28]))/sum(a.[28]) END) as [28],sum(a.[29]),sum(b.[29]),
           (CASE SUM(a.[29])-SUM(b.[29]) WHEN 0 THEN 0 ELSE (sum(a.[29])-sum(b.[29]))/sum(a.[29]) END) as [29],sum(a.[30]),sum(b.[30]),应该在后面紧跟的是
 (CASE SUM(a.[30])-SUM(b.[30]) WHEN 0 THEN 0 ELSE (sum(a.[30])-sum(b.[30]))/sum(a.[30]) END) as [30],sum(a.[31]),sum(b.[31]),(CASE SUM(a.[31])-SUM(b.[31]) WHEN 0 THEN 0 ELSE (sum(a.[31])-sum(b.[31]))/sum(a.[31]) END) as [31]
但由于字符太长而被截断...
这种情况应该怎么解决呢?

Answer 9

感谢 hellowork(一两清风) 和Hopewell_Go(好的在后頭﹗希望更好﹗﹗) 
,(不知不为过，不学就是错！)
现在出现字符串太长...怎么解决呢?
谢谢!

Answer 10

分割...的确应该分割,但原语句是这样:
declare @sql varchar(8000),@sqlall varchar(8000)set @sql =''
select @sql=@sql+'sum(a.['+name+']),sum(b.['+name+']),
           ((sum(a.['+name+'])-sum(b.['+name+']))/case when sum(a.['+name+'])=0 then 1 else sum(a.['+name+']) end) 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)
           ------------------------------------------就这个@sql的字符就太长了....
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 ' 
exec (@sqlall)

Answer 11

这样试试:(把列进行编号,然后按列的编号范围分别生成SQL字符串再连接)
1.
select id = identity(int,1,1),name INTO #TMP from syscolumns where id=object_id('t_costsepc_temp') and name not in('zcname','lot','bh','w','wcname')
2.
declare @sql1 varchar(8000),@sql2 varchar(8000),@sql3 varchar(8000),@sql4....
select @sql1 = '',@sql2 = '',@sql3 = '',@sql4 = '',....select @sql1 = @sql1 + .... from #TMP where id between 1 end 20
select @sql2 = @sql2 + .... from #TMP where id between 21 end 40
......set @sqlall = @sql1 + @sql2 + @sql3 + @sql4....

Answer 12

感谢楼上众多大侠的热情帮助!
问题已解决了...
修改后的语句如下:
select id = identity(int,1,1),name INTO #TMP from syscolumns where id=object_id('t_costsepc_temp') 
and name not in('zcname','lot','bh','w','wcname')declare @sql1 varchar(100),@sql2 varchar(8000),@sql3 varchar(8000),@sql4 varchar(1000)
select @sql1 = '',@sql2 = '',@sql3 = '',@sql4 = '' select @sql2=@sql2+'sum(a.['+name+']),sum(b.['+name+']),
           (CASE SUM(a.[' + name + '])-SUM(b.[' + name + ']) WHEN 0 THEN 0 ELSE (sum(a.['+name+'])-sum(b.['+name+']))/sum(a.['+name+']) END) as ['+name+'],'
from #TMP where  id<=20 select @sql3=@sql3+'sum(a.['+name+']),sum(b.['+name+']),
           (CASE SUM(a.[' + name + '])-SUM(b.[' + name + ']) WHEN 0 THEN 0 ELSE (sum(a.['+name+'])-sum(b.['+name+']))/sum(a.['+name+']) END) as ['+name+'],'
from #TMP where  id>20
set @sql3=left(@sql3,len(@sql3)-1)
drop table #TMPset @sql1='select a.zcname,a.w,a.wcname,' 
set @sql4=' 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 ' 
exec (@sql1+@sql2+@sql3+@sql4)