coalesce関数の使い方
多くの人はISNULL関数を知っていますが、Coalesce関数を知っている人は少ないです.人々は何気なくCoalesce関数を使用し、ISNULLよりも強いことを発見しました.実はこれまで、この関数は確かに非常に役に立ちました.本文は主にその基本的な使用を説明します.
まず、オンライン叢書の簡単な定義を見てみましょう.
パラメータ内の最初の非空式を返します.
構文:
すべてのパラメータがNULLの場合、COALESCEはNULLを返します.少なくとも1つのNull値がNULLタイプである必要があります.ISNULLはCOALESCEに等しいが,それらの挙動は異なる.空でないパラメータを持つISNULLを含む式はNOT NULLとみなされ、空でないパラメータを持つCOALESCEを含む式はNULLとみなされます.SQL Serverで、空でないパラメータを持つCOALESCEを含む式にインデックスを作成するには、次の文に示すように、PERSISTED列プロパティを使用して計算列を永続化します.
[sql] view plain copy print ?
CREATE TABLE #CheckSumTest
(
ID int identity ,
Num int DEFAULT ( RAND() * 100 ) ,
RowCheckSum AS COALESCE( CHECKSUM( id , num ) , 0 ) PERSISTED PRIMARY KEY
);
いくつかの役に立つ例を見てみましょう.
まず、MSDNからこの関数の使い方を見て、coalesce関数(以下、関数と略す)は、パラメータの空でない値を返します.次のようになります.
[sql] view plain copy print ?
SELECT COALESCE(NULL, NULL, GETDATE())
両方のパラメータがnullであるため、getdate()関数の値、すなわち現在の時間を返します.つまり、最初の空でない値を返します.この関数は最初の空でない値を返すため、パラメータには少なくとも空でない値が必要です.次のクエリを使用すると、エラーが発生します.
[sql] view plain copy print ?
SELECT COALESCE(NULL, NULL, NULL)
次に、Pivotに関数を適用すると、次の文がAdventureWorksデータベースで実行されます.
[sql] view plain copy print ?
SELECT Name
FROM HumanResources.Department
WHERE ( GroupName= 'Executive Generaland Administration' )
次の結果が得られます.
結果をねじるには、次の文を使用します.
[sql] view plain copy print ?
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] view plain copy print ?
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] view plain copy print ?
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
結果は次のとおりです.
結果をコピーして一度にすべてのセッションを殺すことができます
まず、オンライン叢書の簡単な定義を見てみましょう.
パラメータ内の最初の非空式を返します.
構文:
COALESCE ( expression [ ,...n ] ) すべてのパラメータがNULLの場合、COALESCEはNULLを返します.少なくとも1つのNull値がNULLタイプである必要があります.ISNULLはCOALESCEに等しいが,それらの挙動は異なる.空でないパラメータを持つISNULLを含む式はNOT NULLとみなされ、空でないパラメータを持つCOALESCEを含む式はNULLとみなされます.SQL Serverで、空でないパラメータを持つCOALESCEを含む式にインデックスを作成するには、次の文に示すように、PERSISTED列プロパティを使用して計算列を永続化します.
[sql] view plain copy print ?
CREATE TABLE #CheckSumTest
(
ID int identity ,
Num int DEFAULT ( RAND() * 100 ) ,
RowCheckSum AS COALESCE( CHECKSUM( id , num ) , 0 ) PERSISTED PRIMARY KEY
);
いくつかの役に立つ例を見てみましょう.
まず、MSDNからこの関数の使い方を見て、coalesce関数(以下、関数と略す)は、パラメータの空でない値を返します.次のようになります.
[sql] view plain copy print ?
SELECT COALESCE(NULL, NULL, GETDATE())
両方のパラメータがnullであるため、getdate()関数の値、すなわち現在の時間を返します.つまり、最初の空でない値を返します.この関数は最初の空でない値を返すため、パラメータには少なくとも空でない値が必要です.次のクエリを使用すると、エラーが発生します.
[sql] view plain copy print ?
SELECT COALESCE(NULL, NULL, NULL)
次に、Pivotに関数を適用すると、次の文がAdventureWorksデータベースで実行されます.
[sql] view plain copy print ?
SELECT Name
FROM HumanResources.Department
WHERE ( GroupName= 'Executive Generaland Administration' )
次の結果が得られます.
結果をねじるには、次の文を使用します.
[sql] view plain copy print ?
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] view plain copy print ?
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] view plain copy print ?
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
結果は次のとおりです.
結果をコピーして一度にすべてのセッションを殺すことができます