全部产品
云市场

条件运算函数

更新时间:2019-08-08 10:01:45

简单CASE表达式

  1. CASE expression
  2. WHEN value THEN result
  3. [ WHEN ... ]
  4. [ ELSE result ]
  5. END
  • 命令说明:从左到右依次查找value,如果找到和expression相等的value则返回对应的result结果;如果未找到相等的value,则返回ELSE语句中result结果。

  • 示例:以下示例将person表中age10的记录的改为50,其他age均加10

    1. select * from hello_mysql_vpc_rds.person
    2. +------+------+----+
    3. | id | name | age |
    4. +-----+------+-----+
    5. | 1| james| 10|
    6. | 2| bond | 20|
    7. | 3| jack | 30|
    8. | 4| lucy | 40|
    9. +------+------+----+
    10. select *, case age when 10 then 50 else(age+10) end as new_age
    11. from hello_mysql_vpc_rds.person
    12. +-----+------+-----+-----+
    13. | id | name | age |new_age
    14. +-----+------+-----+-----+
    15. | 1| james| 10| 50 |
    16. | 2| bond | 20|30 |
    17. | 3| jack | 30|40 |
    18. | 4| lucy | 40|50 |

高级CASE表达式

  1. CASE
  2. WHEN condition THEN result
  3. [ WHEN ... ]
  4. [ ELSE result ]
  5. END
  • 命令说明:从左到右依次计算condition直到第一个为TRUEcondition,返回对应的result结果。如果没有为Truecondition,返回ELSE子句的result结果。

  • 示例:以下示例将person表中age10的记录改为age+10namebondage改为age+20,其他记录的age均改为age+30

    1. select * from hello_mysql_vpc_rds.person
    2. +------+------+----+
    3. | id | name | age |
    4. +-----+------+-----+
    5. | 1| james| 10|
    6. | 2| bond | 20|
    7. | 3| jack | 30|
    8. | 4| lucy | 40|
    9. +------+------+----+
    10. select *, case when age=10 then (age+10) when name='bond' then (age+20) else (age+30) end from hello_mysql_vpc_rds.person
    11. +-----+------+-----+-----+
    12. | id | name | age |new_age
    13. +-----+------+-----+-----+
    14. | 1| james| 10| 20 |
    15. | 2| bond | 20| 40 |
    16. | 3| jack | 30| 60 |
    17. | 4| lucy | 40| 70 |

IF

  1. IF(condition,true_value)
  • 命令说明:如果conditiontrue,返回true_value;否则返回null

  • 示例:

    1. select if((2+3)>4,5);
    2. +-------+
    3. | _col0 |
    4. +-------+
    5. | 5 |
  1. IF(condition,true_value,false_value)
  • 命令说明:如果conditiontrue,返回true_value;否则返回false_value

  • 示例:

    1. select if((2+3)<5,56);
    2. +-------+
    3. | _col0 |
    4. +-------+
    5. | 6 |

IFNULL

  1. IFNULL(expr1,expr2)
  • 命令说明:如果expr1结果不为null,返回expr1的值;否则返回expr2的值。

  • 示例:

    1. select ifnull(NULL,2);
    2. +-------+
    3. | _col0 |
    4. +-------+
    5. | 2 |
    6. select ifnull(1,0);
    7. +-------+
    8. | _col0 |
    9. +-------+
    10. | 1 |

NULLIF

  1. NULLIF(expr1,expr2)
  • 命令说明:如果expr1expr2值相等,返回null;否则返回expr1的值。

  • 示例:

    1. select nullif(2,1);
    2. +-------+
    3. | _col0 |
    4. +-------+
    5. | 2 |
    6. select nullif(2,2);
    7. +-------+
    8. | _col0 |
    9. +-------+
    10. | NULL |

COALESCE

  1. COALESCE(value[, …])
  • 命令说明:从左到右依次查找value,返回第一个非nullvalue

  • 示例:

    1. select coalesce(null,2,3);
    2. +--------------------+
    3. | coalesce(null,2,3) |
    4. +--------------------+
    5. | 2 |

TRY

  1. TRY(expression)
  • 命令说明:计算并返回表达式expression的值,如果计算表达式时遇到错误则返回null

    • 查询数据时,如果您不希望查询过程抛出异常,可以使用TRY函数屏蔽异常。

    • TRY函数遇到异常时默认返回null,您可以使用COALESCE函数指定TRY函数遇到异常时的返回值。

    • TRY函数可以处理以下错误:

      • 除0。
      • 无效的转换或者无效的函数参数。
      • 数值大小超出规定范围。
  • 示例:

    1. select try(3/0);
    2. +----------+
    3. | try(3/0) |
    4. +----------+
    5. | NULL |
    1. select try(3/2);
    2. +----------+
    3. | try(3/2) |
    4. +----------+
    5. | 1.5 |

    源数据中包含非法数据:

    1. select * from shipping;
    2. ---------------------------------------------------
    3. origin_state | origin_zip | packages | total_cost
    4. --------------+------------+----------+------------
    5. California | 94131 | 25 | 100
    6. California | P332a | 5 | 72
    7. California | 94025 | 0 | 155
    8. New Jersey | 08544 | 225 | 490

    查询中不使用TRY函数时,查询失败。

    1. select cast(origin_zip as BIGINT) from shipping;
    2. ---------------------------------------------------
    3. Query failed: Can not cast 'P332a' to BIGINT

    查询中使用TRY函数时,查询不会报错。

    1. select try(cast(origin_zip as BIGINT)) from shipping;
    2. ------------
    3. origin_zip
    4. ------------
    5. 94131
    6. NULL
    7. 94025
    8. 08544

    COALESCE嵌套TRY函数,查询报错时将返回指定值。

    1. select coalesce(try(total_cost/packages), 0) as per_package from shipping;
    2. -------------
    3. per_package
    4. -------------
    5. 4
    6. 14
    7. 0
    8. 19

GREATEST

  1. GREATEST(value1,value2,...)
  • 命令说明:返回参数中的最大值。

  • 示例:

    1. select greatest(2,0);
    2. +---------------+
    3. | greatest(2,0) |
    4. +---------------+
    5. | 2 |
    1. select greatest('B','A','C');
    2. +-----------------------+
    3. | greatest('B','A','C') |
    4. +-----------------------+
    5. | C |

LEAST

  1. LEAST(value1,value2,...)
  • 命令说明:返回参数中的最小值。

  • 示例:

    1. select least('B','A','C');
    2. +--------------------+
    3. | least('B','A','C') |
    4. +--------------------+
    5. | A |
    1. select least(34.0,3.0,5.0,767.0);
    2. +---------------------------+
    3. | least(34.0,3.0,5.0,767.0) |
    4. +---------------------------+
    5. | 3.0 |

NVL2

  1. NVL2(expr1,expr2,expr3)
  • 命令说明:如果expr1的结果为非null值,将返回expr2的值;如果expr1的结果为null值,将返回expr3的值。

  • 示例:

    1. select nvl2(1, 2, 3);
    2. +---------------+
    3. | nvl2(1, 2, 3) |
    4. +---------------+
    5. | 2 |
    1. select nvl2(null, 2, 3);
    2. +------------------+
    3. | nvl2(null, 2, 3) |
    4. +------------------+
    5. | 3 |

DECODE

  1. DECODE(expr,search1,result1,search2,result2,...searchn,resultn,default)
  • 命令说明:从左到右依次查找,如果expr的值等于search1则返回result1,如果expr的值等于search2则返回result2,以此类推,如果没有与expr相等的result,则返回default

  • 示例:

    1. select decode(1, 1, '1A', 2, '2A', '3A');
    2. +-----------------------------------+
    3. | decode(1, 1, '1A', 2, '2A', '3A') |
    4. +-----------------------------------+
    5. | 1A |
    1. select decode(1, 2, '1A', 1, '2A', '3A');
    2. +-----------------------------------+
    3. | decode(1, 2, '1A', 1, '2A', '3A') |
    4. +-----------------------------------+
    5. | 2A |
    1. select decode(1, 2, '1A', 2, '2A', '3A');
    2. +-----------------------------------+
    3. | decode(1, 2, '1A', 2, '2A', '3A') |
    4. +-----------------------------------+
    5. | 3A |