case...when...和MySql、Oracle、SqlServer 处理空值

2019独角兽企业重金招聘Python工程师标准>>>

1、Case具有两种格式，简单Case函数和Case搜索函数。

--简单Case函数
CASE sex
     WHEN '1' THEN '男'
     WHEN '2' THEN '女'
ELSE '其他' END
--Case搜索函数
CASE WHEN sex = '1' THEN '男'
     WHEN sex = '2' THEN '女'
ELSE '其他' END

2、判断某字段是否为null，和判断是否为字符或数字时的写法不一样，如果不注意，很容易搞错

--错误方法:
CASE columnName WHEN null THEN 0 ELSE columnName END

--正确方法:
CASE WHEN columnName is null THEN 0 ELSE columnName END

3、MYSQL IFNULL(expr1,expr2)          
如果expr1不是NULL，IFNULL()返回expr1，否则它返回expr2。IFNULL()返回一个数字或字符串值，取决于它被使用的上下文环境。 

mysql> select IFNULL(1,0);      
                     -> 1      
mysql> select IFNULL(0,10);      
                     -> 0      
mysql> select IFNULL(1/0,10);      
                     -> 10      
mysql> select IFNULL(1/0,yes);      
                     -> yes     

    IF(expr1,expr2,expr3)          
如果expr1是TRUE(expr1<>0且expr1<>NULL)，那么IF()返回expr2，否则它返回expr3。IF()返回一个数字或字符串值，取决于它被使用的上下文。

mysql> select IF(1>2,2,3);      
                     -> 3      
mysql> select IF(1<2,yes,no);      
                     -> yes   

expr1作为整数值被计算，它意味着如果你正在测试浮点或字符串值，你应该使用一个比较操作来做。

mysql> select IF(0.1,1,0);      
                     -> 0      
mysql> select IF(0.1<>0,1,0);      
                     -> 1  

在上面的第一种情况中，IF(0.1)返回0，因为0.1被变换到整数值, 导致测试IF(0)。这可能不是你期望的。在第二种情况中，比较测试原来的浮点值看它是否是非零，比较的结果被用作一个整数。 

4、用于处理 NULL 值的函数

      MySQL  -  IFNULL() 函数

      Oracle   -  NVL() 函数

      SqlServer  -  ISNULL()函数

5、Oracle中函数NVL,NVL2,NULLIF,COALESCE    

     NVL函数的格式如下：NVL(expr1,expr2)

含义是：如果oracle第一个参数为空那么显示第二个参数的值，如果第一个参数的值不为空，则显示第一个参数本来的值。

     NVL2函数的格式如下：NVL2(expr1,expr2, expr3)

含义是：如果该函数的第一个参数为空那么显示第二个参数的值，如果第一个参数的值不为空，则显示第三个参数的值。

    NULLIF函数的格式如下：NULLIF(exp1,expr2)

含义是：如果exp1和exp2相等则返回空(NULL)，否则返回第一个值。 

    Coalesce函数格式如下：Coalesce(expr1, expr2, expr3….. exprn)

含义是：指定多个表达式的占位符，所有表达式必须是相同类型，或者可以隐性转换为相同的类型。
返回表达式中第一个非空表达式，如果所有自变量均为 NULL，则 COALESCE 返回 NULL 值。

6、SqlServer函数ISNULL()

    ISNULL ( check_expression , replacement_value )

含义是：如果 check_expression 不为 NULL，则返回它的值；否则，在将 replacement_value 隐式转换为 check_expression 的类型（如果这两个类型不同）后，则返回前者。

转载于:https://my.oschina.net/u/3496297/blog/1592989