[翻译][.NET Tip of The Day]用 COALESCE 函数来替代一长串的 CASE WHEN ... ELSE 语句(T-SQL)

译自:http://dotnettipoftheday.org/tips/sql-coalesce.aspx

用 COALESCE 函数来替代一长串的 CASE WHEN ... ELSE 语句(T-SQL)

(COALESCE function instead of long CASE WHEN ... ELSE (T-SQL))

当你希望找出一个不为NULL的值时, 你可以使用 COALESCE 函数, 而不是使用一长串 "SELECT ... CASE WHEN ... ELSE ..." construction, . 让我们来看看下面这段 T-SQL 语句, 其中我们希望找出一个有效的(available) "source":

SELECT TheSource =

   CASE

      WHEN localSource IS NOT NULL THEN localSource

      WHEN intranetSource IS NOT NULL THEN intranetSource

      WHEN internetSource IS NOT NULL THEN internetSource

      ELSE ''

   END

FROM ...

现在我们用 COALESCE 函数来重写上面这段代码:

SELECT TheSource =

   COALESCE(localSource, intranetSource, internetSource, '')

FROM ...

这条提示适用于MS SQL Server 2000/2005.

1/15/2008