一、mysql查询语句对于为null和为空字符串给出特定值处理
- SELECT if(IFNULL(filedName,"指定字符串")="","指定字符串",filedName) '重命名的字符名' FROM tableName;
- 在mysql中：
SELECT `s`.`id`,`s`.`sku`,`s`.`barcode`,`s`.`goods_name`,
`s`.`short_name`,`unit`,u.serial_number unitNumber,
gb.serial_number brandNumber,`kind`,c.serial_number
cateNumber,`type_name`,`prop_name`,`s`.`status`,`s`.`sale_price`,
IF (ifnull (brand_name,'') = '', '', brand_name) brand_name
FROM `t_goods_sku` `s`
LEFT JOIN `t_goods` `g` ON `s`.`goods_id`=`g`.`id`
LEFT JOIN `t_goods_category` `c` ON `c`.`id`=`g`.`cate_id`
LEFT JOIN `t_goods_unit` `u` ON `u`.`id`=`g`.`unit_id`
LEFT JOIN `t_goods_brand` `gb` ON `gb`.`id`=`g`.`brand_id`
WHERE ( `s`.`id` IN ('52','341','347') ) AND `s`.`delete_time` = 0
- thinphp6中：
$info['goodsList'] = $skuObj->alias('s')
->field("s.id,s.sku,s.barcode,s.goods_name,s.short_name,unit,
u.serial_number unitNumber,gb.serial_number brandNumber,
kind,c.serial_number cateNumber,type_name,prop_name,s.status,s.sale_price")
->fieldRaw("IF (ifnull (brand_name,'') = '', '', brand_name) brand_name") // 处理值为null的情况
->leftJoin('goods g', "s.goods_id = g.id")
->leftJoin("goods_category c", "`c`.`id` = `g`.`cate_id`")
->leftJoin("goods_unit u", "`u`.`id` = `g`.`unit_id`")
->leftJoin("goods_brand gb", "`gb`.`id` = `g`.`brand_id`")
->where('s.id','in',$info['sku_ids'])
->select();
