IF表达式 IF(expr1,expr2,expr3)
- 作为在SQL级别进行统一的if判断,处理特定场景逻辑
如果表达式expr1为true(且不等于0和Null),则if语句返回表达式expr2
,否则返回表达式expr3
,返回值是数字或字符串。强调的地方,表达式1、2、3:即可以是简单表达式,也可以是表字段。下面进行举例:
简单表达式返回
例如select *,if(gender=1,"男","女") as genderStr from student where gender != ''
把字段作为表达式返回值
例如select id,user_id,user_name,status,if(status = "Starting",min_price,max_price) as activityPrice from goods where 1=1
实战:实际业务SQL案例
SELECT
td.id id,
td.link_name linkName,
td.link_type linkType,
td.link_value linkValue,
td.background_image backgroundImage,
g.id goodsId,
g.name as goodsName,
g.preview_image previewImage,
g.min_price minPrice,
g.sale_begin_time saleBeginTime,
g.sale_end_time saleEndTime,
a.type activityType,
// 把字段作为if表达式的返回值
if(a.status = 'STARTING',a.activity_min_price,g.min_price) as activityMinPrice,
a.status activityStatus
FROM
topic_detail td
INNER JOIN goods g ON g.id = td.link_value
INNER JOIN goods_scope gscp ON gscp.goods_id = g.id AND gscp.scope IN (1,441900,440000)
LEFT JOIN activity a ON a.id = g.activity_id
WHERE
td.is_deleted = 0
AND td.is_show = 1
AND g.salable = 1
AND g.deleted = 0
AND g.status = 'SHELFING'
AND CURRENT_TIMESTAMP between g.sale_begin_time and g.sale_end_time
AND td.topic_module_id = 99
ORDER BY td.weight DESC