通过case 语句 解决 SQL 遇到被零除错误
简单 CASE 函数：
CASE input_expression WHEN when_expression THEN result_expression [ ...n ] [
ELSE else_result_expressionEND
通过CONVERT 解决%问题
使用 CONVERT：
CONVERT (data_type[(length)], expression [, style])
举例如下：
1: select ShopCode,
2: Sum(Cost) as SumCost,
3: Sum(OutCost) as SumOutCost,
4: Sum(SaleAmt) as SumSaleAmt,
5: Sum(DscTotal) as SumDscTotal,
6: Sum(OthSaleAmt) as SumOthSaleAmt,
7: Sum(OthDscTotal) as SumOthDscTotal,
8: Sum(AdjTotal) as SumAdjTotal,
9: --这句是关键 第一个 算出毛利率 % 第二个出现除数为0 的时候 除数变成0.01 *100 变成1 呵呵 解决 SQL 遇到被零除错误
10: rtrim(convert(numeric(18,2),((Sum(SaleAmt)-Sum(Cost)-Sum(AdjTotal))/
(case when Sum(SaleAmt)=0 then 0.01 else (saleAmt) end)*100)))+'%',
11: Sum(VipDscOf + OthVipDscOf) as SumVipDscOf
12: from ShopSaleDep
13: where (YWDate >= '2011-06-01' and YWDate <= '2011-06-02')
14: group by ShopCode
15:
16: -----销售汇总 MS - SQL 2000 中遇到