mysql case实例:分组检测条件值

发布时间:2020-03-05编辑:脚本学堂
本文介绍下,mysql中case语句的一个例子,可以分组检测条件值,有需要的朋友参考下。

mysql case语句的例子,代码如下:

mysql> DELIMITER //
mysql> CREATE FUNCTION myFunction (delivery_day INT(1)) RETURNS INT(2) //创建mysql函数
    -> BEGIN
    ->
    -> DECLARE shipping_cost INT(2) DEFAULT 0;
    ->
    -> CASE delivery_day
    -> WHEN 1 THEN
    ->         SET shipping_cost = 20;
    -> WHEN 2 THEN
    ->         SET shipping_cost = 15;
    -> WHEN 3 THEN
    ->         SET shipping_cost = 10;
    -> ELSE
    ->         SET shipping_cost = 5;
    -> END CASE;
    -> RETURN shipping_cost;
    ->
    -> END
    -> //
Query OK, 0 rows affected (0.00 sec)

mysql> DELIMITER ;
mysql>
mysql> select myFunction(1);
+---------------+
| myFunction(1) |
+---------------+
|            20 |
+---------------+
1 row in set (0.00 sec)

mysql>
mysql> select myFunction(2);
+---------------+
| myFunction(2) |
+---------------+
|            15 |
+---------------+
1 row in set (0.00 sec)

mysql>
mysql> select myFunction(3);
+---------------+
| myFunction(3) |
+---------------+
|            10 |
+---------------+
1 row in set (0.00 sec)
//删除mysql函数
mysql> drop function myFunction;
Query OK, 0 rows affected (0.00 sec)