数据库查询语句中的空值处理
|
在数据库中经常需要对空值（NULL）做处理，比如“如果名称为空值则返回别名”，甚至还有更复杂的需求，比如“如果名称为空值则返回别名，如果别名也为空则返回‘佚名’两个字”、“如果名称为与别名相等则返回空值，否则返回名称”。这些需求已经带有流程控制的色彩了，一般来说需要在宿主语言中使用流程控制语句来进行处理，可是如果是在报表程序等大数据量的程序中把这些任务交给宿主语言的话会大大降低运行速度，因此我们必须想办法在SQL这一层进行处理。 COALESCE()函数 主流数据库系统都支持COALESCE()函数，这个函数主要用来进行空值处理，其参数格式如下： COALESCE ( expression,value1,value2……,valuen) COALESCE()函数的第一个参数expression为待检测的表达式，而其后的参数个数不定。 COALESCE()函数将会返回包括expression在内的所有参数中的第一个非空表达式。如果expression不为空值则返回expression；否则判断value1是否是空值，如果value1不为空值则返回value1；否则判断value2是否是空值，如果value2不为空值则返回value3；……以此类推，如果所有的表达式都为空值，则返回NULL。 我们将使用COALESCE()函数完成下面的功能，返回人员的“重要日期”：如果出生日 期不为空则将出生日期做为“重要日期”，如果出生日期为空则判断注册日期是否为空，如果注册日期不为空则将注册日期做为“重要日期”，如果注册日期也为空则将“2008年8月8日”做为“重要日期”。实现此功能的SQL语句如下： MYSQL、MSSQLServer、DB2: SELECT FName,FBirthDay,FRegDay, COALESCE(FBirthDay,FRegDay,'2008-08-08') AS ImportDay FROM T_Person Oracle: SELECT FBirthDay,FRegDay, COALESCE(FBirthDay,FRegDay,TO_DATE('2008-08-08', 'YYYY-MM-DD HH24:MI:SS')) AS ImportDay FROM T_Person COALESCE()函数的简化版 COALESCE()函数可以用来完成几乎所有的空值处理，不过在很多数据库系统中都提 供了它的简化版，这些简化版中只接受两个变量，其参数格式如下： MYSQL: IFNULL(expression,value) MSSQLServer: ISNULL(expression,value) Oracle: NVL(expression,value) 这几个函数的功能和COALESCE(expression,value)是等价的。 NULLIF()函数 主流数据库都支持NULLIF()函数，这个函数的参数格式如下：NULLIF ( expression1 , expression2 )如果两个表达式不等价，则NULLIF 返回第一个expression1的值。如果两个表达式等价，则NULLIF 返回第一个expression1类型的空值。也就是返回类型与第一个expression相同。 下面的示例创建 budgets 表，表中显示部门 (dept) 及其当年的预算 (current_year) 以及去年预算 (previous_year)。对于当年预算，那些同去年相比预算没有改变的部门使用NULL，那些预算还没有确定的部门使用 0。要只计算那些接收预算的部门的预算平均值，并引用上一年度的预算值（当 current_year 为 0 时mssql 空值，使用 previous_year 值），请组合使用 NULLIF 和 COALESCE 函数。 USE pubs IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'budgets') DROP TABLE budgets GO SET NOCOUNT ON CREATE TABLE budgets ( depttinyint IDENTITY, current_yeardecimal NULL, previous_year decimal NULL) INSERT budgets VALUES(100000, 150000) INSERT budgets VALUES(NULL, 300000) INSERT budgets VALUES(0, 100000) INSERT budgets VALUES(NULL, 150000) INSERT budgets VALUES(300000, 250000) GO SET NOCOUNT OFF SELECT AVG(NULLIF(COALESCE(current_year, previous_year), 0.00)) AS 'Average Budget' FROM budgets GO 下面是结果集： Average Budget ---------------------------------------- 212500.000000 CASE函数 COALESCE()函数只能用来进行空值的逻辑判断处理，如果要实现“如果年龄大于25 则返回姓名，否则返回别名”这样的逻辑判断就比较麻烦了。在主流数据库系统中提供了CASE函数的支持，严格意义上来讲CASE函数已经是流程控制语句了，不是简单意义上的函数，不过为了方便，很多人都将CASE称作“流程控制函数”。CASE函数有两种用法，下面分别介绍。 用法一： CASE expression WHEN value1 THEN returnvalue1 WHEN value2 THEN returnvalue2 WHEN value3 THEN returnvalue3 …… ELSE defaultreturnvalue END CASE函数对表达式expression进行测试，如果expression等于value1则返回returnvalue1，如果expression等于value2则返回returnvalue2，expression等于value3则返回returnvalue3，……以此类推，如果不符合所有的WHEN条件，则返回默认值defaultreturnvalue。 CASE函数在制作报表的时候非常有用。比如表T_Customer中的FLevel字段是整数类型，它记录了客户的级别，如果为1则是VIP客户，如果为2则是高级客户，如果为3则是普通客户，在制作报表的时候显然不应该把1、2、3这样的数字显示到报表中，而应该显示相应的文字，这里就可以使用CASE函数进行处理，SQL语句如下： SELECT FName, (CASE FLevel WHEN 1 THEN 'VIP客户' WHEN 2 THEN '高级客户' WHEN 3 THEN '普通客户' ELSE '客户类型错误' END) as FLevelName FROM T_Customer 用法二： CASE WHEN condition1 THEN returnvalue1 WHEN condition 2 THEN returnvalue2 WHEN condition 3 THEN returnvalue3 …… ELSE defaultreturnvalue END 其中的condition1 、condition 2、condition 3……为条件表达式，CASE函数对各个表达式从前向后进行测试，如果条件condition1为真则返回returnvalue1，否则如果条件condition2为真则返回returnvalue2，否则如果条件condition3为真则返回returnvalue3，……以此类推，如果不符合所有的WHEN条件，则返回默认值defaultreturnvalue。 这种用法中没有限制只能对一个表达式进行判断，因此使用起来更加灵活。比如如下的SQL语句用来判断一个人的体重是否正常，如果体重小于40则认为太瘦，而如果体重大于50则认为太胖，介于40和50之间则认为是正常： SELECT FName, FWeight, (CASE WHEN FWeightWHEN FWeight>50 THEN 'fat' ELSE 'ok' END) as isnormal FROM T_Person end. （编辑：泰州站长网） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |
