MySql中IF函数总结
IF
IF函数是MySQL控制流函数之一,它根据一个条件返回一个值。IF函数有时被称为IF ELSE或IF THEN ELSE函数。
IF函数语法如下:
IF(expr,if_true_expr,if_false_expr)
IF函数通常与SUM()函数组合:
SELECT
SUM(IF(status = 'Shipped', 1, 0)) AS Shipped,
SUM(IF(status = 'Cancelled', 1, 0)) AS Cancelled
FROM
orders;
IF函数与COUNT()函数组合,因为COUNT函数不计算空值,所以如果状态不在选定状态,IF函数将返回NULL,否则将返回1:
SELECT
COUNT(IF(status = 'Cancelled', 1, NULL)) Cancelled,
COUNT(IF(status = 'Disputed', 1, NULL)) Disputed,
COUNT(IF(status = 'In Process', 1, NULL)) 'In Process',
COUNT(IF(status = 'On Hold', 1, NULL)) 'On Hold',
COUNT(IF(status = 'Resolved', 1, NULL)) 'Resolved',
COUNT(IF(status = 'Shipped', 1, NULL)) 'Shipped'
FROM
orders;