带有加法运算符的MySQL中的IF / ELSE语句

Question

I have been trying to understand doing this in MySQL but have to admit defeat even after reading several tutorials and the manual on it. Any help would be gratefully appreciated.

I have three fields of decimal type (for currency). These are 'base_price', 'discounted_price' and 'extras_price'.

I am trying to create the MYSQL needed to complete the following pseudo-code:

if (discounted_price is not null or 0) {
    total_price = discounted_price + extras_price
} else {
    total_price = base_price + extras_price
}

It may be worthy of note that this will be used to create a virtual field for CakePHP 2.* Thanks for any help you can offer.

Answer 1

Is this what you are looking for?

select (case when discounted_price is not null and discounted_price <> 0
             then discounted_price + extras_price
             else base_price + extras_price
        end) as total_price
from t;

By the way, the first condition is redundant in SQL. You can write this as:

select ((case when discounted_price <> 0 then discounted_price else base_price end) +  
        extras_price) as total_price
from t;