全部产品
阿里云办公

条件运算函数

更新时间:2017-10-11 17:41:26

CASE

标准SQL中 CASE 表达式有两种结构。 简单的 CASE 表达式会从左到右依次查找 value 直到找到和 expression 相等的:

  1. CASE expression
  2. WHEN value THEN result
  3. [ WHEN ... ]
  4. [ ELSE result ]
  5. END

找到相等的 value 后会返回对应的 result 结果。 如果没有找到相等的 value,则会返回 ELSE 语句的 result 结果。 例如:

  1. SELECT a,
  2. CASE a
  3. WHEN 1 THEN 'one'
  4. WHEN 2 THEN 'two'
  5. ELSE 'many'
  6. END

高级的 CASE 表达式会从左到右依次计算 condition 直到第一个为 TRUEcondition,并返回对应的 result 结果。

  1. CASE
  2. WHEN condition THEN result
  3. [ WHEN ... ]
  4. [ ELSE result ]
  5. END

如果没有为 Truecondition,则 ELSE 子句的 result 会返回。 例如:

  1. SELECT a, b,
  2. CASE
  3. WHEN a = 1 THEN 'aaa'
  4. WHEN b = 2 THEN 'bbb'
  5. ELSE 'ccc'
  6. END

IF

IF 函数是和以下 CASE 表达式效果相同的语言结构:

  1. CASE
  2. WHEN condition THEN true_value
  3. [ ELSE false_value ]
  4. END

if(condition, true_value)

计算返回 true_value 如果 conditionTRUE, 否则返回 NULL

if(condition, true_value, false_value)计算返回 true_value 如果 conditionTRUE, 否则计算返回 false_value

COALESCE

coalesce(value[, …])

返回第一个非 NULL 的 value。 和 CASE 表达式类似, 参数仅在需要的时候计算。

IFNULL

If expr1 is not NULL, IFNULL() returns expr1; otherwise it returns expr2.

支持的语法:

IFNULL(expr1,expr2)

例子:

  1. SELECT IFNULL(1,0);
  2. SELECT IFNULL(NULL,10);

NULLIF

nullif(value1, value2)返回 NULL 如果 value1 等于 value2, 否则返回 value1

支持的语法:

NULLIF(expr1,expr2)

例子:

  1. SELECT NULLIF(1,1);
  2. SELECT NULLIF(1,2);

TRY

try(expression)

计算返回表达式 expression 如果表达式计算遇到错误则返回 NULL

当你不希望查询抛出异常的时候,可以使用 TRY 函数屏蔽异常。TRY 函数在遇到异常的时候会返回 NULL, 如果希望在查询出错的时候返回默认值,可以 使用 COALESCE 函数指定默认值。

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

  • 除0
  • Invalid cast or function argument
  • 数值大小超出范围
例子

源数据表包含非法数据:

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

不用 TRY 函数导致查询失败:

  1. SELECT CAST(origin_zip AS BIGINT) FROM shipping;
  1. Query failed: Can not cast 'P332a' to BIGINT

TRY 函数返回 NULL:

  1. SELECT TRY(CAST(origin_zip AS BIGINT)) FROM shipping;
  1. origin_zip
  2. ------------
  3. 94131
  4. NULL
  5. 94025
  6. 08544
  7. (4 rows)

不用 TRY 函数导致查询失败:

  1. SELECT total_cost / packages AS per_package FROM shipping;
  1. Query failed: / by zero

COALESCE 嵌套 TRY 函数返回默认值:

  1. SELECT COALESCE(TRY(total_cost / packages), 0) AS per_package FROM shipping;
  1. per_package
  2. -------------
  3. 4
  4. 14
  5. 0
  6. 19
  7. (4 rows)

Control Flow 函数MySQL兼容性(ADS 已经支持的MySQL函数)

COALESCE

Returns the first non-NULL value in the list, or NULL if there are no non-NULL values.

支持的语法:

COALESCE(value,...)

例子:

  1. SELECT COALESCE(NULL,1);
  2. SELECT COALESCE(NULL,NULL,NULL);
GREATEST

With two or more arguments, returns the largest (maximum-valued) argument.

支持的语法:

GREATEST(value1,value2,...)

例子:

  1. SELECT GREATEST(2,0);
  2. SELECT GREATEST(34.0,3.0,5.0,767.0);
  3. SELECT GREATEST('B','A','C');
LEAST

With two or more arguments, returns the smallest (minimum-valued) argument.

支持的语法:

LEAST(value1,value2,...)

例子:

  1. SELECT LEAST(2,0);
  2. SELECT LEAST(34.0,3.0,5.0,767.0);
  3. SELECT LEAST('B','A','C');

Control Flow 函数Oracle兼容性(ADS 已经支持的Oracle函数)

NVL2

NVL2 lets you determine the value returned by a query based on whether a specified expression is null or not null. If expr1 is not null, then NVL2 returns expr2. If expr1 is null, then NVL2 returns expr3.

支持的语法:

NVL2(expr1, expr2, expr3)

例子:

  1. SELECT NVL2(1, 2, 3);
  2. SELECT NVL2(NULL, 2, 3);
DECODE

DECODE compares expr to each search value one by one. If expr is equal to a search, then AnalyticDB returns the corresponding result.

支持的语法:

DECODE(expr, search, result, default)

例子:

  1. SELECT DECODE(1, 1, '1A', 2, '2A', '3A');
  2. SELECT DECODE(2, 1, '1A', 2, '2A', '3A');