Case when and ifnull judgment in mysql

Case when and ifnull judgment in mysql

Mysql can be used to query and update SQL statements. The following describes the usage of case when and ifnull based on these two SQL statements:

Case when

update t_tradefee set margin_profit = (case when market_price is null then (2.3-basic_fee)*remain_amount else  (2.3-market_price)*remain_amount+margin_profit END),market_price = 2.3 where substring(cardno,1,3)='001' and trade_type = 1

Judgment during the update process, ifmarket_priceIf it is null, the value of set margin_profit is(2.3-basic_fee)*remain_amountOtherwise, the value of set margin_profit is2.3-market_price)*remain_amount+margin_profit

Usage: case when... Else... End

Ifnull

select day(op_time) as day,sum(price) as price,IFNULL((select sum(price) from t_margin_profit where op_time<'2016-1-1 00:00:00' ),0) as old from t_margin_profit  where year(op_time)='2016' and month(op_time)='1' GROUP BY day(op_time)

Value assignment in the query
Usage: IFNULL (exp1, exp2): If the value of exp1 is not empty, take the value of exp1; otherwise, take the value of exp2.