[Oracle] COALESCE的用法

素华7071

--COALESCE用法
--COALESCE (expression_1, expression_2, ...,expression_n)依次参考各参数表达式，遇到非null值即停止并返回该值。如果所有的表达式都是空值，最终将返回一个空值。
--1. 建立普通的表
WITH T_CUST AS
(
SELECT '张一' CUST_NM, '18610881234' PHONE_NUMBER1, '' PHONE_NUMBER2, '' PHONE_NUMBER3 FROM DUAL
UNION ALL
SELECT '张二' CUST_NM, '' PHONE_NUMBER1, '010-82828383' PHONE_NUMBER2, '' PHONE_NUMBER3 FROM DUAL
UNION ALL
SELECT '张三' CUST_NM, '' PHONE_NUMBER1, '13911091109' PHONE_NUMBER2, '010-85858585' PHONE_NUMBER3 FROM DUAL
UNION ALL
SELECT '张四' CUST_NM, NULL PHONE_NUMBER1, NULL PHONE_NUMBER2, NULL PHONE_NUMBER3 FROM DUAL   
)
SELECT * FROM T_CUST;



  --2. 利用COALESCE
WITH T_CUST AS
(
SELECT '张一' CUST_NM, '18610881234' PHONE_NUMBER1, '' PHONE_NUMBER2, '' PHONE_NUMBER3 FROM DUAL
UNION ALL
SELECT '张二' CUST_NM, '' PHONE_NUMBER1, '010-82828383' PHONE_NUMBER2, '' PHONE_NUMBER3 FROM DUAL
UNION ALL
SELECT '张三' CUST_NM, '' PHONE_NUMBER1, '13911091109' PHONE_NUMBER2, '010-85858585' PHONE_NUMBER3 FROM DUAL
UNION ALL
SELECT '张四' CUST_NM, NULL PHONE_NUMBER1, NULL PHONE_NUMBER2, NULL PHONE_NUMBER3 FROM DUAL      
)
SELECT CUST_NM, COALESCE(PHONE_NUMBER1, PHONE_NUMBER2, PHONE_NUMBER3) AS CUST_PHONE FROM T_CUST;



  --3. 利用COALESCE后处理NULL值
WITH T_CUST AS
(
SELECT '张一' CUST_NM, '18610881234' PHONE_NUMBER1, '' PHONE_NUMBER2, '' PHONE_NUMBER3 FROM DUAL
UNION ALL
SELECT '张二' CUST_NM, '' PHONE_NUMBER1, '010-82828383' PHONE_NUMBER2, '' PHONE_NUMBER3 FROM DUAL
UNION ALL
SELECT '张三' CUST_NM, '' PHONE_NUMBER1, '13911091109' PHONE_NUMBER2, '010-85858585' PHONE_NUMBER3 FROM DUAL
UNION ALL
SELECT '张四' CUST_NM, NULL PHONE_NUMBER1, NULL PHONE_NUMBER2, NULL PHONE_NUMBER3 FROM DUAL      
)
SELECT CUST_NM, NVL(COALESCE(PHONE_NUMBER1, PHONE_NUMBER2, PHONE_NUMBER3), 'NO') AS CUST_PHONE FROM T_CUST;