IF 用法

IF()函数在条件为TRUE时返回一个值，如果条件为FALSE则返回另一个值

语法：

sql">IF(条件, 条件为真时的返回值, 条件为假时的返回值)

 示例:

sql">SELECT  id, name, score, IF (score >= 60, '及格', '不及格' ) as  grade FROM  students;

---

IFNULL 用法

IFNULL()函数它接受两个参数，如果不是NULL，则返回第一个参数。 否则，IFNULL函数返回第二个参数

 语法：

sql">IFNULL(expression_1,expression_2);

 示例:

sql">SELECT IFNULL(1,0);   #返回 1

sql">SELECT IFNULL('',1);  # 返回 ''

sql">SELECT IFNULL(NULL,'IFNULL function');  #返回 'IFNULL function'

---

CASE用法

Case语句在MySQL中的灵活性和高效性，可以根据不同的场景快速地判断执行逻辑并返回对应的结果或更新操作。在实际应用中，还可以结合其他语句或函数来进行更为复杂的操作，如IF语句、SUM函数等

语法

sql">CASE expressionWHEN value1 THEN result1WHEN value2 THEN result2...ELSE else_result
END

进阶语法

sql">CASEWHEN condition1 THEN result1WHEN condition2 THEN result2...ELSE else_resultEND

 示例:

sql">SELECT column1,CASE column2WHEN 'value1' THEN 'result1'WHEN 'value2' THEN 'result2'ELSE 'result3'END AS new_columnFROM table_name;

综合示例

sql">SELECTc.`spu` AS '编辑商品',a.`edit_desc` AS '编辑语言',f.`name` AS '人员部门',d.`uname` AS '采集人',b.`uname` AS '编辑人',( CASE WHEN d.activated = 'false' THEN '是'WHEN d.deleted_at != NULL  THEN '是'WHEN d.id != b.id THEN '否'ELSE IF ( d.id != b.id , '否', '是' )END) AS '首编',c.`created_at` AS '采集时间',a.`created_at` AS '完毕时间',( CASE WHEN d.activated = 'false' THEN '禁用'WHEN d.deleted_at != NULL  THEN '禁用'ELSE IF ( d.id, '正常', '禁用' )END) AS '账号状态'FROM`product_edit_langauge` AS aLEFT JOIN `users` AS b ON a.`uid` = b.`id`LEFT JOIN `products` AS c ON c.`id` = a.`product_id`LEFT JOIN `users` AS d ON d.`id` = c.`uid`LEFT JOIN `users_to_dep` AS e ON e.`uid` = a.`uid`LEFT JOIN `departments` AS f ON f.`id` = e.`depid` 
WHEREa.`edit_langid` != 1 AND b.`deleted_at` IS NULL AND b.`activated` = 'true' AND a.`created_at` >= '2024-04-10 0:00:00' AND a.`created_at` <= '2024-04-10 23:59:59'