ろご

SQL Server CLR-条件合致データ件数取得-

概要

引数として比較対象数値、':'(コロン)、条件をNVARCHARで連結したものをもらい、
比較対象数値が条件を満たしていた場合、該当件数のカウント対象とする集合関数です。

条件に設定する演算子

この関数に設定する演算子には以下のものをサポートしています。
それ以外のものはエラーになります。
  • < (より大きい)
  • > (より小さい)
  • = (等しい)
  • <= (以下)
  • >= (以上)
  • != (等しくない)
  • <> (等しくない)
  • !< (より小さくない)
  • !> (より大きくない)

使用例

対象テーブルデータ サンプル
Test3
idnamevalueexp
1Cさん101>100
2Dさん100>=100
3Cさん100>=100
4Cさん100>=100
5Cさん100!>100

				
select name, dbo.CountIf(
cast( coalesce([value],'') as nvarchar) + ':' + [exp] )
from
Test3 
group by [name] ;
				
			

結果
name件数
Cさん2
Dさん1

Cさんのデータでvalueがexpを満たすのはid=1,5の2件

仕様

ユーザー定義集計関数として作成してあります。
とりうる値はINT(INT32)の範囲としてください。

クラス名は『Wankuma.Kaya.CountIf』です。

ダウンロード

ダウンロードはこちらよりお願いします。 DL

動作確認

Microsoft Visual C# 2005にてコンパイルし、
SQL Server 2005 Expressにて動作確認をしています。

使用上の注意

SQL CLRを動かせるようになるまでに、苦労されるかもしれません。
以下のことを行ってください。
  • DLLのダウンロード、保存
  • アセンブリの登録(safeで登録してください。)
  • CLR 統合機能の有効化
  • プロシージャ作成

アセンブリ登録方法

アセンブリの登録は『CREATE ASSEMBLY』を使用して行うか、『Microsoft SQL Server Management Studio』を使用して登録します。

CREATE ASSEMBLYの例はこちらです。
ファイル名にはダウンロードしたDLLファイルへのパスを記述してください。
					
CREATE ASSEMBLY [アセンブリ名]
FROM 'ファイル名'
WITH PERMISSION_SET = SAFE
					
				

Microsoft SQL Server Management Studioでの操作は以下の通りです。
画像をクリックして拡大表示にて参照してください
手順1
手順2

CLR 統合機能の有効化

					
sp_configure 'clr enabled', 1
GO
RECONFIGURE
GO
					
				

プロシージャ実行ユーザへの権限付与

					
GRANT UNSAFE ASSEMBLY TO [ユーザ名];
GO
					
				

データベースへの信頼設定

					
ALTER DATABASE [データベース名] SET TRUSTWORTHY ON;
GO
					
				

ファンクション作成

				
CREATE AGGREGATE CountIf (@cmp nvarchar(max)) RETURNS int
EXTERNAL NAME [アセンブリ名].[Wankuma.Kaya.CountIf]
				
			




Copyrightc 夏椰@わんくま同盟 All Rights Reserved.