MySQL CASE表达式是一个流程控制结构,用在在SELECT、WHERE等语句中根据条件动态构造内容。
2种MySQL CASE格式
MySQL的CASE表达式有2中形式,一种更像是编程语言当中的CASE语句,拿一个给定的值(变量)跟一系列特定的值作比较,我称之为CASE类型。另一种则更像是编程语言中的if语句,当满足某些条件的时候取特定值,我称之为IF类型。
CASE类型
此类型的语句结构如下:
CASE value
WHEN compare_value_1 THEN result_1
WHEN compare_value_2 THEN result_2
…
ELSE result END
此情况下,拿value与各个compare_value比较,相等时取对应的值,都不相等时取最后的result。如下例子:
SELECT
CASE position
WHEN '主任医师' THEN 1
ELSE 0
END AS is_p
FROM
doctor
根据position的取值,决定返回的结果是1或者0。
IF类型
IF类型的CASE表达式如下:
CASE
WHEN condition_1 THEN result_1
WHEN condition_2 THEN result_2
…
ELSE result END
此时自上而下根据condition判断,取对应的值,都不满足的时候取最后的result。下面是一个例子:
SELECT
customerName, state, country
FROM
customers
ORDER BY (CASE
WHEN state IS NULL THEN country
ELSE state
END);
上述SQL,如果state不为NULL,则根据country字段排序,否则使用state字段排序。 这里不涉及到值得比较,而是条件是否满足。需要注意的是,这里的state is null
是针对每一条记录的,也就是说,最后的排序操作相当于先计算一个中间排序字段:
order_field = status IS NULL ? country : state
然后在ORDER BY order_field
最后的结果可能如下:
另一个例子,动态觉得聚合函数的输入:
SELECT
SUM(CASE
WHEN status = 'Shipped' THEN 1
ELSE 0
END) AS 'Shipped',
SUM(CASE
WHEN status = 'On Hold' THEN 1
ELSE 0
END) AS 'On Hold',
SUM(CASE
WHEN status = 'In Process' THEN 1
ELSE 0
END) AS 'In Process',
SUM(CASE
WHEN status = 'Resolved' THEN 1
ELSE 0
END) AS 'Resolved',
SUM(CASE
WHEN status = 'Cancelled' THEN 1
ELSE 0
END) AS 'Cancelled',
SUM(CASE
WHEN status = 'Disputed' THEN 1
ELSE 0
END) AS 'Disputed',
COUNT(*) AS Total
FROM
orders;
CASE表达式返回值
CASE表达式最终返回的值,取决于上下文,例如比较的是字符串,则返回的也是字符串类型。
如果省略了ELSE语句,并且各个条件都没满足,则返回NULL
CASE与IFNULL
MySQL中还有一个IFNULL表达式,其格式如下:
IFNULL(expression_1,expression_2);
其逻辑为:如果expression_1 不为null,则返回expression_1,否则返回expression_2.
例如:
SELECT
IFNULL(education, '-1') AS e_type
FROM
doctor;
上述语句中,如果education is null ,则返回-1,反则返回-1。
如果使用CASE来表达:
SELECT
CASE
WHEN education IS NULL THEN '-1'
ELSE education
END AS e_type
FROM
doctor;
CASE与IF
事实上,IFNULL的通用形式是IF表达式,其格式为
IF(condition,expression_1,expression_2)
类似于Java中的三元操作符,其逻辑为:如果condition为true,返回expression_1,否则 返回expression_2.
例如:
SELECT
IF(education IS NULL, '-1' , '1') AS e_type
FROM
doctor;
如果使用CASE来表达:
SELECT
CASE
WHEN education IS NULL THEN '-1'
ELSE '1'
END AS e_type
FROM
doctor;