coalesce搭配nullif使用

with t1 as
(
  select NUll as col1, '' as col2, 'aaa' as col3
)
--关于COALESCE用法 当col1 为 Null时候返回 col2 依次类推 当col2 为Null时 返回col3
--很多朋友会以为这里返回col3 其实不是。想要返回col3, 需搭配nullif函数。nullif(col1,''): 如果col1='' 则返回Null 否则 返回col1
--select COALESCE(col1, col2, col3) from t1

select COALESCE(nullif(col1,''), nullif(col2,''), nullif(col3,'')) from t1

nullif详尽用法，参见

https://docs.microsoft.com/en-us/sql/t-sql/language-elements/nullif-transact-sql?view=sql-server-2017 

Coalesce详尽用法，参见

https://docs.microsoft.com/en-us/sql/t-sql/language-elements/coalesce-transact-sql?view=sql-server-2017

转载于:https://www.cnblogs.com/adsoft/p/11526872.html