例如有如下用户表(TB_USER):
id |
name |
tel_home |
tel_company |
phone |
1 |
张三 |
010-123456 |
|
|
2 |
李四 |
|
010-88888888 |
|
3 |
王二 |
|
|
13366666666 |
该表中记录了各用户的家庭电话(tel_home)、公司电话(tel_company)、手机号码(phone).
由于用户信息登记不全,现在希望得到所有用户的某一登记过的联系方式即可,
可使用如下语句:
SELECT name,COALESCE(tel_home,tel_company,phone) telephone FROM TB_USER;
得到结果如下:
name |
telephone |
张三 |
010-123456 |
李四 |
010-88888888 |
王二 |
13366666666 |
【函数解释】:
数据库应用软件很多时候将多重的、相关的实体信息保存在同一个表格中。例如,购买的零件和本地生产的零件都是零件,经理和工人都是员工,尽管多重的实体类型在数据存储上略有不同,但是它们有太多的相同之处,因此通常使用一个表格而不是两个。
处理这样的表格通常意味着对每一行进行条件测试来检查哪个实体类型正在被处理,然后返回每种类型的不同结果集。CASE语句可以用来完成这一工作。
从Oracle 9i版开始,COALESCE函数在很多情况下就成为替代CASE语句的一条捷径,COALESCE的格式如下:
COALESCE (expression_1, expression_2, ...,expression_n)
列表中第一个非空的表达式是函数的返回值,如果所有的表达式都是空值,最终将返回一个空值。
使用COALESCE的秘密在于大部分包含空值的表达式最终将返回空值(连接操作符“||”是一个值得注意的例外)。例如,空值加任何值都是空值,空值乘任何值也都是空值,依此类推。
这样您就可以构建一系列表达式,每个表达式的结果是空值或非空,这就像一个CASE语句,表达式按照顺序进行测试,第一个满足条件的值就确定了结果。
列表A展示了名为PARTS的表格的一部分,该表格存储了购买的零件数据和生产的零件数据,如果是购买的零件,那么part_type列的值为‘P’,如果是本地生产或组装的则是‘B’;此外,对于购买的零件,purchase_cost 列会显示购买成本,而本地生产的零件则是空值;而且,本地生产的零件还有material_qty和material_cost两列的信息,对于购买的零件则是空值。
您可以使用一个CASE语句来测试part_type列的值并返回either purchase_cost和material_qty列与material_cost列的乘积;不过COALESCE可以用一行语句解决这个问题:
COALESCE(purchase_cost, material_qty * material_cost)
如果数据行中存储的是一个购买的零件,那么purchase_cost就不是空值,将返回purchase_cost的值;然而,对于本地生产的零件,purchase_cost是空值,COALESCE会忽略它,然后将material_qty和material_cost相乘,并将乘积作为结果返回。
SELECT part_id "Part", part_type "Type",
COALESCE(purchase_cost, material_qty * material_cost) "Cost"
FROM parts;
您可以对任何数量的表达式重复使用这个模式,COALESCE是一个非常便捷的方法对统一表格中的多重实体求值。
最后,还要说一点CASE语句的优点,就是CASE是自动进行文档记录的,这便于理解和解读正在发生的事情。
分享到:
相关推荐
使用oracle数据库的coalesce函数
很多人知道ISNULL函数,但是很少人知道Coalesce函数,人们会无意中使用到Coalesce函数,并且发现它比ISNULL更加强大,其实到目前为止,这个函数的确非常有用,本文主要讲解其中的一些基本使用: 首先看看联机丛书...
要求:显示出每个供应商供应每种零件的数量合计和每个供应商供应所有零件的数量合计以及所有供应商供应的所有零件数量合计。 数据表 链接: spj库 分析 使用WITH ROLLUP:在group分组字段的基础上进行统计数据。 函数...
Transact-SQL中的对空值的处理函数COALESCE.pdf
2. 非空查找函数: COALESCE 22 3. 条件判断函数:CASE 22 4. 条件判断函数:CASE 22 七、字符串函数 23 1. 字符串长度函数:length 23 2. 字符串反转函数:reverse 23 3. 字符串连接函数:concat 23 4. 带分隔符字符...
ISNULL()、NVL()、IFNULL() 和 COALESCE() 函数的几种用法,
COALESCE()函数可以接受一系列的值,如果列表中所有项都为空(null),那么只使用一个值。然后,它将返回第一个非空值。这一技巧描述了创造性使用SQL Server 中COALESCE()函数的两种方法。 这里有一个简单的例子...
NULL 博文链接:https://seabird1979.iteye.com/blog/2172579
主要介绍了MySql COALESCE函数使用方法代码案例,文中通过示例代码介绍的非常详细,对大家的学习或者工作具有一定的参考学习价值,需要的朋友可以参考下
Oracle中的NVL,NVL2,NULLIF,COALESCE通用函数
在mysql中,其实有不少方法和函数是很有用的,这次介绍一个叫coalesce的,拼写十分麻烦,但其实作用是将返回传入的参数中第一个非null的值
coalesce与coalescy 等待等待时使用的两个“ coalescy” php函数?? 操作员 : coalesce :PHP的简单空null合并函数coalescy :与coalesce一样,但返回第一个不虚假的值。 还有两个函数可以在不E_NOTICE情况下获取...
COALESCE它会返回列表中第一个非NULL的值,或者,如果不存在非NULL的值,它就会返回一个NULL INTERVALINTERVAL运算符提供另外一种对数字及其范围的控制。它接受一个数字列表(N0, N1, N2...),将N0与剩下的所有数字...
主要给大家介绍了关于mysql中替代null的IFNULL()与COALESCE()函数的相关资料,文中通过示例代码介绍的非常详细,对大家具有一定的参考学习价值,需要的朋友们下面来一起看看看吧。
学习oracle,首先我们必须要掌握常用的基本命令,oracle中的命令比较多,常用的命令如下: 1. 登录命令(sqlplus) 说明:用于登录到oracle数据库 用法:sqlplus 用户名/密码 [as sysdba/sysoper] 注意:当用特权用户...
返回参数中的第一个非空表达式(从左向右依次类推); 使用示例 a,b,c三个变量。 select coalesce(null,2,3); // Return 2 select coalesce(null,null,3); // Return 3 select coalesce(1,2,3); // Return 1 通过...