在写sql语句时,可能会遇到如何对数据表中的某列数据进行简单统计,比如对总价这一个字段进行分段打折,那么我们应该如何实现呢?
可以通过case…when…then…的方式实现:
case when 条件1 then 语句1
when 条件2 then 语句2
...
else 语句 end
样例如下:
Given a relation schema: shippingPricing(id,price,amt1,disc1,amt2,disc2,).
For price between amt1 and amt 2, disc 1 is applied; and to the amount above amt2, disc2 is applied
select shipper,
(case when price>amt2 then amt1+(amt2-amt1)*(1-disc1)+(price-amt2)*(1-disc2)
when price<amt2 and price>amt1 then amt1+(price-amt1)*(1-disc1)
else price end
) as discost
from shippingPricing