以下第一行是表头，本来应该有3条数据的，但某天“注”这条数据是0，不用统计，结果悲剧了，下面的SQL语句报除数为0的错误，童鞋们帮忙看看该怎么写才好。
property qcsl_cost okasl_cost
高 27286.1200 22741.4400
滚 35392.3100 31483.0300
select '总'
+cast(cast(sum(a.qcsl_cost) as decimal(5,0)) as varchar)
+';良'+cast(cast(sum(a.okasl_cost) as decimal(5,0)) as varchar)
+';率'+cast(cast(sum(a.okasl_cost)*100.0/sum(a.qcsl_cost) as decimal(4,1)) as varchar)+'%;'
+';高率'+
cast(cast(sum(case when a.property='高' then a.okasl_cost else 0 end)*100.0
/sum(case when a.property='高' then a.qcsl_cost else 0 end) as decimal(4,1)) as varchar)+'%'
+';滚率'+
cast(cast(sum(case when a.property='滚' then a.okasl_cost else 0 end)*100.0
/sum(case when a.property='滚' then a.qcsl_cost else 0 end) as decimal(4,1)) as varchar)+'%'
+';注率'+
cast(cast(sum(case when a.property='注' then a.qcsl_cost else 0 end)*100.0
/sum(case when a.property='注' then a.okasl_cost else 0 end) as decimal(4,1)) as varchar)+'%'
from #t a
------解决方案--------------------
你最后注率再判断 如果除数为0 则显示没有统计数据...
------解决方案--------------------
create table #t
(property varchar(10),qcsl_cost decimal(20,5),okasl_cost decimal(20,5))
insert into #t
select '高',27286.1200,22741.4400 union all
select '滚',35392.3100,31483.0300
select '总'
+cast(cast(sum(a.qcsl_cost) as decimal(5,0)) as varchar)
+';良'+cast(cast(sum(a.okasl_cost) as decimal(5,0)) as varchar)
+';率'+cast(cast(sum(a.okasl_cost)*100.0/sum(a.qcsl_cost) as decimal(4,1)) as varchar)+'%;'
+';高率'+
case when sum(case when a.property='高' then a.qcsl_cost else 0 end)<>0 then
cast(cast(sum(case when a.property='高' then a.okasl_cost else 0 end)*100.0
/sum(case when a.property='高' then a.qcsl_cost else 0 end) as decimal(4,1)) as varchar)+'%'
else ' 没有统计数据 ' end
+';滚率'+
case when sum(case when a.property='滚' then a.qcsl_cost else 0 end)<>0 then
cast(cast(sum(case when a.property='滚' then a.okasl_cost else 0 end)*100.0
/sum(case when a.property='滚' then a.qcsl_cost else 0 end) as decimal(4,1)) as varchar)+'%'
else ' 没有统计数据 ' end
+';注率'+
case when sum(case when a.property='注' then a.okasl_cost else 0 end)<>0 then
cast(cast(sum(case when a.property='注' then a.qcsl_cost else 0 end)*100.0
/sum(case when a.property='注' then a.okasl_cost else 0 end) as decimal(4,1)) as varchar)+'%'
else ' 没有统计数据 ' end 'output'
from #t a
/*
output
---------------------------------------------------------------
总62678;良54224;率86.5%;;高率83.3%;滚率89.0%;注率 没有统计数据
(1 row(s) affected)
*/