<转>SQL函数——COALESCE
时间:2022-03-10 17:38
转载地址:#
目录
- COALESCE ( expression [ ,...n ] )
- CREATE TABLE #CheckSumTest
- (
- ID int identity ,
- Num int DEFAULT ( RAND() * 100 ) ,
- RowCheckSum AS COALESCE( CHECKSUM( id , num ) , 0 ) PERSISTED PRIMARY KEY
- );
- SELECT COALESCE(NULL, NULL, GETDATE())
- SELECT COALESCE(NULL, NULL, NULL)
- SELECT Name
- FROM HumanResources.Department
- WHERE ( GroupName= ‘Executive Generaland Administration‘ )
- DECLARE @DepartmentName VARCHAR(1000)
- SELECT @DepartmentName = COALESCE(@DepartmentName, ‘‘) + Name + ‘;‘
- FROM HumanResources.Department
- WHERE ( GroupName= ‘Executive Generaland Administration‘ )
- SELECT @DepartmentName AS DepartmentNames
- 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
- 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
如果所有参数均为 NULL,则 COALESCE 返回 NULL。至少应有一个 Null 值为 NULL 类型。尽管 ISNULL 等同于 COALESCE,但它们的行为是不同的。包含具有非空参数的 ISNULL 的表达式将视为 NOT NULL,而包含具有非空参数的 COALESCE 的表达式将视为 NULL。在 SQL Server 中,若要对包含具有非空参数的 COALESCE 的表达式创建索引,可以使用 PERSISTED 列属性将计算列持久化,如以下语句所示:
[sql]
CREATE TABLE #CheckSumTest ( ID int identity , Num int DEFAULT ( RAND() * 100 ) , RowCheckSum AS COALESCE( CHECKSUM( id , num ) , 0 ) PERSISTED PRIMARY KEY );
下面来看几个比较有用的例子:
首先,从MSDN上看看这个函数的使用方法,coalesce函数(下面简称函数),返回一个参数中非空的值。如:
[sql]
SELECT COALESCE(NULL, NULL, GETDATE())
由于两个参数都为null,所以返回getdate()函数的值,也就是当前时间。即返回第一个非空的值。由于这个函数是返回第一个非空的值,所以参数里面必须最少有一个非空的值,如果使用下面的查询,将会报错:
[sql]
SELECT COALESCE(NULL, NULL, NULL)
然后来看看把函数应用到Pivot中,下面语句在AdventureWorks 数据库上运行:
[sql]
SELECT Name FROM HumanResources.Department WHERE ( GroupName= ‘Executive Generaland Administration‘ )
会得到下面的结果:
如果想扭转结果,可以使用下面的语句:
[sql]
DECLARE @DepartmentName VARCHAR(1000) SELECT @DepartmentName = COALESCE(@DepartmentName, ‘‘) + Name + ‘;‘ FROM HumanResources.Department WHERE ( GroupName= ‘Executive Generaland Administration‘ ) SELECT @DepartmentName AS DepartmentNames
使用函数来执行多条SQL命令:
当你知道这个函数可以进行扭转之后,你也应该知道它可以运行多条SQL命令。并且使用分号来区分独立的操作。下面语句是在Person架构下,有名字为Name的列的值:
[sql]
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
还有一个很重要的功能:。当你尝试还原一个库,并发现不能独占访问时,这个功能非常有效。我们来打开多个窗口,来模拟一下多个连接。然后执行下面的脚本:
[sql]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
结果如下:
然后你可以把结果复制出来,然后一次性杀掉所有session。
<转>SQL函数——COALESCE,布布扣,bubuko.com