Mysql 控制语句与条件语句

IF 用法

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

语法：

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

 示例:

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

---

IFNULL 用法

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

 语法：

IFNULL(expression_1,expression_2);

 示例:

SELECT IFNULL(1,0);   #返回 1

SELECT IFNULL('',1);  # 返回 ''

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

---

CASE用法

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

语法

CASE expression
    WHEN value1 THEN result1
    WHEN value2 THEN result2
    ...
    ELSE else_result
END

进阶语法

CASE

    WHEN condition1 THEN result1

    WHEN condition2 THEN result2

    ...

    ELSE else_result

END

 示例:

SELECT column1,

   CASE column2

      WHEN 'value1' THEN 'result1'

      WHEN 'value2' THEN 'result2'

      ELSE 'result3'

   END AS new_column

FROM table_name;

综合示例

SELECT
	c.`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 a
	LEFT 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` 
WHERE
	a.`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'