空值处理

所属分类：[容器相关] | 创建时间：2013-01-13 | 文章属性：原创 | 文章来源：http://thoughtfly.iteye.com | 作者：windfly

COALESCE

  

 

返回其参数中第一个非空表达式。
   

 
 

语法
   

 
COALESCE   (   expression   [   ,...n   ]   )
    
 
 

参数
   

 
expression   
 
 

任何类型的表达式。
   

 
  n
  
 
 

表示可以指定多个表达式的占位符。所有表达式必须是相同类型，或者可以隐性转换为相同的类型。
   

 
 

返回类型
   

 

将相同的值作为
   expression
  
返回。
   

 
 

注释
   

 

如果所有自变量均为
   NULL
，则
   COALESCE   
返回
   NULL   
值。
   

 
 
COALESCE(expression1,...n)   
与此
   CASE   
函数等价：
   

 
 
CASE   
 
      WHEN   (expression1   IS   NOT   NULL)
  THEN   expression1   
 
      ...   
 
      WHEN   (expressionN   IS   NOT   NULL)
  THEN   expressionN   
 
      ELSE   NULL   
 
 
 
 
 

示例
   

 

在下面的示例中，显示包含三列有关某个雇员每年工资收入信息的

  wages   
表：
hourly_wage
、
salary   
和
   commission
。但是，每个雇员只能接受一种付款方式。若要确定支付给所有雇员的工资总额，请使用
   COALESCE   
函数接受在
   hourly_wage
、
salary
  
和
   commission
  
中找到的非空值。
   

 
  SET
  NOCOUNT   ON   
  GO
  
  USE
  master   
  IF
  EXISTS   (SELECT   TABLE_NAME   FROM  
INFORMATION_SCHEMA.TABLES     
 
            WHERE   TABLE_NAME   =
  'wages')   
 
      DROP   TABLE   wages   
  GO
  
 
CREATE   TABLE   wages   
  (
  
 
      emp_id             tinyint
        identity,   
 
      hourly_wage       decimal    
  NULL,   
 
      salary             decimal
        NULL,   
 
      commission       decimal    
  NULL,   
 
      num_sales       tinyint    
  NULL   
  )
  
  GO
  
 
INSERT   wages   VALUES(10.00,   NULL,   NULL,   NULL)
  
 
INSERT   wages   VALUES(20.00,   NULL,   NULL,   NULL)
  
 
INSERT   wages   VALUES(30.00,   NULL,   NULL,   NULL)
  
 
INSERT   wages   VALUES(40.00,   NULL,   NULL,   NULL)
  
 
INSERT   wages   VALUES(NULL,   10000.00,   NULL,  
NULL)   
 
INSERT   wages   VALUES(NULL,   20000.00,   NULL,  
NULL)   
 
INSERT   wages   VALUES(NULL,   30000.00,   NULL,  
NULL)   
 
INSERT   wages   VALUES(NULL,   40000.00,   NULL,  
NULL)   
 
INSERT   wages   VALUES(NULL,   NULL,   15000,   3)
  
 
INSERT   wages   VALUES(NULL,   NULL,   25000,   2)
  
 
INSERT   wages   VALUES(NULL,   NULL,   20000,   6)
  
 
INSERT   wages   VALUES(NULL,   NULL,   14000,   4)
  
  GO
  
  SET
  NOCOUNT   OFF   
  GO
  
 
SELECT   CAST(COALESCE(hourly_wage   *   40   *   52,
    
 
      salary,     
 
      commission   *   num_sales)   AS  
money)   AS   'Total   Salary'     
 
FROM   wages   
  GO
  
 
 

下面是结果集：
   

 
 
Total   Salary     
 
------------     
 
20800.0000   
 
41600.0000   
 
62400.0000   
 
83200.0000   
 
10000.0000   
 
20000.0000   
 
30000.0000   
 
40000.0000   
 
45000.0000   
 
50000.0000   
 
120000.0000   
 
56000.0000   
 
  (12
  row(s)   affected)   
 
 
语法
   

 
  
ISNULL   
(    check_expression    ,   
replacement_value    )
     

 
  
参数
   

  
check_expression   
 
  

将被检查是否为
    NULL
的表达式。
check_expression    
可以是任何类型的。
   

 
  
replacement_value   
 
  

在
   
check_expression    
为
    NULL
时将返回的表达式。
replacement_value    
必须与
    check_expresssion    
具有相同的类型。
     

 
  

返回类型
   

  

返回与
   
check_expression    
相同的类型。
   

 
  
注释
   

  

如果
   
check_expression    
不为
    NULL
，那么返回该表达式的值；否则返回
    replacement_value
。
   

 
  

示例
   

  
A.    
将
   
ISNULL    
与
   
AVG    
一起使用
   

  

下面的示例查找所有书的平均价格，用值
   
$10.00    
替换
   
titles    
表的
   
price    
列中的所有
   
NULL    
条目。
   

 
  
USE    pubs   
  
GO   
  
SELECT    AVG(ISNULL(price,   
$10.00))   
  
FROM    titles   
  
GO   
 
  

下面是结果集：
   

 
  
--------------------------     
  
14.24                                               
 
  
(1    row(s)    affected)   
 
  
B.    
使用
   
ISNULL   

  

下面的示例为
   
titles    
表中的所有书选择书名、类型及价格。如果一个书名的价格是
    NULL
，那么在结果集中显示的价格为
    0.00
。
   

 
  
USE    pubs   
  
GO   
  
SELECT    SUBSTRING(title,   
1,    15)    AS   
Title,    type    AS   
Type,     
        
ISNULL(price,    0.00)    AS   
Price   
  
FROM    titles   
  
GO   
 
  

下面是结果集：
   

 
  
Title                       
Type                   
Price                       
  
---------------    ------------   
--------------------------     
  
The    Busy    Execut   
business           
19.99                                               
  
Cooking    with    Co   
business           
11.95                                               
  
You    Can   
Combat     
business           
2.99                                                 
  
Straight    Talk    A   
business           
19.99                                               
  
Silicon    Valley     
mod_cook           
19.99                                               
  
The    Gourmet    Mic   
mod_cook           
2.99                                                 
  
The    Psychology     
UNDECIDED         
0.00                                                 
  
But    Is    It   
User      popular_comp   
22.95                                               
  
Secrets    of    Sili   
popular_comp   
20.00                                               
  
Net    Etiquette       
popular_comp   
0.00                                                 
  
Computer    Phobic   
psychology       
21.59                                               
  
Is    Anger    the   
En    psychology       
10.95                                               
  
Life    Without    Fe   
psychology       
7.00                                                 
  
Prolonged    Data     
psychology       
19.99                                               
  
Emotional    Secur   
psychology       
7.99                                                 
  
Onions,    Leeks,     
trad_cook         
20.95                                               
  
Fifty    Years   
in     
trad_cook         
11.95                                               
  
Sushi,    Anyone?     
trad_cook         
14.99                                               
 
  
(18    row(s)    affected)   
 
 
 
1.MSSQL: ISNULL()
 
2.Oracle: NVL()
 
3.Mysql: IFNULL()
 
 
在SQL Server Oracle
MySQL 当数据库 中查出某值为NULL怎么办? 
 
1.MSSQL: ISNULL()
 
 
语法
 
ISNULL (
check_expression , replacement_value ) 
 
参数
 
check_expression 
 
将被检查是否为
NULL的表达式。check_expression 可以是任何类型的。 
 
replacement_value 
 
在 check_expression 为
NULL时将返回的表达式。replacement_value 必须与 check_expresssion 具有相同的类型。 
 
返回类型
 
返回与 check_expression
相同的类型。 
 
注释
 
如果 check_expression
不为 NULL，那么返回该表达式的值；否则返回 replacement_value。 
 
 
2.Oracle: NVL()
 
 
语法
 
 
NVL(eExpression1,
eExpression2) 
 
参数
 
eExpression1,
eExpression2 
 
如果 eExpression1
的计算结果为 null 值，则 NVL() 返回 eExpression2。如果 eExpression1 的计算结果不是 null 值，则返回
eExpression1。eExpression1 和 eExpression2 可以是任意一种数据类型。如果 eExpression1 与
eExpression2 的结果皆为 null 值，则 NVL( ) 返回 NULL 
 
返回值类型
 
 
字符型、日期型、日期时间型、数值型、货币型、逻辑型或
null 值 
 
说明
 
 
在不支持 null 值或 null
值无关紧要的情况下，可以使用 NVL( ) 来移去计算或操作中的 null 值。 
 
3.Mysql: IFNULL()
 
 
语法
 
IFNULL(expr1,expr2) 
 
参数
 
expr1,expr2 
假如expr1不是NULL，IFNULL()返回expr1，否则它返回expr2。IFNULL()返回一个数字或字符串值，取决于它被使用的上下文环境。