MySQL控制流

MySQL控制流有：CASE、IF、IFNULL、NULLIF

1.CASE

CASE有2种格式，可以用在SELECT或WHERE字句中。

CASE value WHEN compare_value_1 THEN result_1 WHEN compare_value_2 THEN result_2 … ELSE result END 或者 CASE WHEN condition_1 THEN result_1 WHEN condition_2 THEN result_2 … ELSE result END

 如果不指定ELSE的返回值，默认返回NULL。

SELECT customerName, state, country FROM customers

ORDER BY (CASE

    WHEN state IS NULL THEN country

    ELSE state

END);

用case统计数量:

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;

2.IF

2.1 IF(expr,if_true_expr,if_false_expr) 如：SELECT IF(1 = 2,'true','false'); -- false SELECT IF(1 = 1,' true','false'); -- true

 

SELECT

    customerNumber,

    customerName,

    IF(state IS NULL, 'N/A', state) state,

    country

FROM

    customers;

2.2

IF expression THEN    statements; END IF;

IF expression THEN    statements; ELSE    else-statements; END IF;   或者 IF expression THEN    statements; ELSEIF elseif-expression THEN    elseif-statements; ... ELSE    else-statements; END IF;

 举个例子：

DELIMITER $$

CREATE PROCEDURE GetCustomerLevel(

    in  p_customerNumber int(11),

    out p_customerLevel  varchar(10))

BEGIN

    DECLARE creditlim double;

    SELECT creditlimit INTO creditlim

    FROM customers

    WHERE customerNumber = p_customerNumber;

    IF creditlim > 50000 THEN

SET p_customerLevel = 'PLATINUM';

    ELSEIF (creditlim <= 50000 AND creditlim >= 10000) THEN

        SET p_customerLevel = 'GOLD';

    ELSEIF creditlim < 10000 THEN

        SET p_customerLevel = 'SILVER';

    END IF;

END$$

 

3.IFNULL

IFNULL(expression_1,expression_2);

IFNULL接受2个参数，如果第一个参数不为null，则返回第一个参数，否则，返回第二个参数。

 

1	SELECT IFNULL('',1); -- returns '' SELECT IFNULL(1,0); -- returns 1 SELECT IFNULL(NULL,'IFNULL function'); -- returns IFNULL function

 

2 3 4

SELECT     contactname, IFNULL(bizphone, homephone) phone FROM     contacts;

 4.NULLIF

NULLIF(expression_1,expression_2);

当第一个参数等于第二个参数时，返回NULL，否则，返回第一个参数。

SELECT NULLIF(1,1); -- return NULL SELECT NULLIF(1,2); -- return 1

使用场景：防止除数为0

1  2  3 4 5 6 7

SELECT     SUM(IF(status = 'Shipped', 1, 0)) /     NULLIF(SUM(IF(status = 'Cancelled', 1, 0)), 0) FROM     orders WHERE     orderDate BETWEEN '2003-06-01' AND '2003-06-30';