Coalesce()函数的全新认识

人们经常遇到Coalesce （接合）函数，而他们大都把它看做是ISNULL 的一个更有效的形式。事实上，我发现它是非常有用的函数而却只有很少的文档。在这篇文章里，笔者将为你展示Coalesce 的基本使用以及一些你可能从没见过的特性。

　　让我们首先从coalesce具有文档记录的使用开始。根据MSDN，coalesce返回它的参数中第一个非空表达式。

　　例如，

　　

      SELECT COALESCE(NULL, NULL, NULL, GETDATE()) 　　它将返回当前的日期。它略过第一个NULL值并返回第一个非空的值。

 

　　对Pivot(透视)使用Coalesce

　　如果你对AdventureWorks数据库运行下面的语句：

　　

      SELECT Name

　　FROM HumanResources.Department

　　WHERE (GroupName = 'Executive General and Administration')

　　你将获得像下面的一个标准结果集。

 

     

　　图1

　　如果你想透视数据，你可以运行下面的命令。

　　

      DECLARE @DepartmentName VARCHAR(1000)

　　SELECT @DepartmentName = COALESCE(@DepartmentName,'') + Name + ';'

　　FROM HumanResources.Department

　　WHERE (GroupName = 'Executive General and Administration')

　　SELECT @DepartmentName AS DepartmentNames

　　并获得下面的结果集。

 

     

　　图2

　　使用Coalesce来执行多条SQL语句

　　如果你可以使用coalesce语句来透视数据，那么就可能通过透视数据和使用一个分号来分隔这些操作从而运行多条SQL语句。假设你想找到Person schema 中任何字段名称为“Name”的字段的值。如果你执行下面的脚本它就起到这种作用。

DECLARE @SQL VARCHAR(MAX)

　　CREATE TABLE #TMP

　　(Clmn VARCHAR(500),

　　Val VARCHAR(50))

　　SELECT @SQL=COALESCE(@SQL,'')+CAST('INSERT INTO #TMP Select ''' + TABLE_SCHEMA + '.' + TABLE_NAME + '.'

　　+ COLUMN_NAME + ''' AS Clmn, Name FROM ' + TABLE_SCHEMA + '.[' + TABLE_NAME +

　　'];' AS VARCHAR(MAX))

　　FROM INFORMATION_SCHEMA.COLUMNS

　　JOIN sysobjects B ON INFORMATION_SCHEMA.COLUMNS.TABLE_NAME = B.NAME

　　WHERE COLUMN_NAME = 'Name'

　　AND xtype = 'U'

　　AND TABLE_SCHEMA = 'Person'

　　PRINT @SQL

　　EXEC(@SQL)

　　SELECT * FROM #TMP

　　DROP TABLE #TMP

　　下面是结果集。

     

　　图3

　　我个人喜欢能够使用三行代码杀掉数据库中的所有事务。如果你曾经试过恢复数据库而不能获得专门的访问，你就知道这是多么有用的了。

　　

      DECLARE @SQL VARCHAR(8000)

　　SELECT @SQL=COALESCE(@SQL,'')+'Kill '+CAST(spid AS VARCHAR(10))+ '; '

　　FROM sys.sysprocesses

　　WHERE DBID=DB_ID('AdventureWorks')

　　PRINT @SQL --EXEC(@SQL) Replace the print statement with exec to execute

　　这将得出一个类似于下面的结果集。