[Oracle] COALESCE的用法

  --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;