SQLSERVER EXISTS、NOT EXISTS

SQLSERVER EXISTS、NOT EXISTS

EXISTS と NOT EXISTS

サブクエリの導入にキーワード EXISTS を使用した場合、そのサブクエリは「存在検査」として機能します。
NOT キーワードを付けた場合、そのサブクエリは「非存在検査」として機能します。

サブクエリの WHERE 句では、
・ データ抽出されるテーブル と 存在を試されるテーブル との リンク条件を指定します。
・ 存在を試されるテーブル の検索条件を指定します。
このことにより、このサブクエリから返される行が存在するかどうかがテストされます。
サブクエリは実際にはデータを生成せず、TRUE または FALSE の値を返します。

WHERE [NOT] EXISTS (subquery)

EXISTS演算子とは

EXISTS 演算子は、副問い合せの結果が存在するかを調べるときに使用します。
このとき、副問い合せの結果が存在するとき真になります。

NOT EXISTS演算子とは

NOT EXISTS 演算子は、副問い合せの結果が存在しないかを調べるときに使用します。
このとき、副問い合せの結果が存在しないとき真になります。

EXISTS、NOT EXISTS のテストサンプル

以下にテストSQLを載せておきます。
@tbl1には、id,kbn,pointの三項目
@tbl2には、id,kbn,priceの三項目
point,priceはランダムで数値が入ります。

@tbl1 と @tbl2は、同じid(kbnも)が入ります。
@tbl2のpriceの条件に合わせて、存在する存在しないをテストし、@tbl1のレコードを取得しています。

declare @tbl1 table(
    id int,
    kbn nchar(10) ,
    point int
)

declare @tbl2 table(
    id int,
    kbn nchar(10) ,
    price int
)

declare @start_id as int
set @start_id=0
 
while @start_id < 10
    begin
        print @start_id
        insert into @tbl1 values(@start_id, @start_id % 3, @start_id * Rand() * 10)
        insert into @tbl2 values(@start_id, @start_id % 3, @start_id * Rand() * 100)
        set @start_id = @start_id +1
    end

select * from @tbl1
select * from @tbl2

/* EXISTS */
/* テーブル@tbl2のpriceが200より大きなレコードを取得し、@tbl1の該当するレコードを表示する*/
select * from @tbl1 t1
  where EXISTS (
   select * from @tbl2 t2
     where t1.id= t2.id     --レコードのリンク
     and t2.price >= 200  --条件
 );


/* NOT EXISTS */
/* テーブル@tbl2のpriceが200より大きなレコードを取得し、@tbl1の該当しないレコードを表示する*/
select * from @tbl1 t1
  where NOT EXISTS (
    select * from @tbl2 t2
    where t1.id= t2.id     --レコードのリンク
    and t2.price >= 200  --条件
 );

IN 句と INNER JOIN 句 と比較

ちなみに、EXISTはINよりも速いとか、EXISTSは INNER JOIN より遅いだとか、良く耳にしますが、実際どうなんでしょうね?
個人的には、条件や状況によって違うのではないかと考えています。
Indexの有無、テーブルの大きさ、暗黙的な変換の有無、直前に使用テーブルにアクセスしてた等々条件や状況によって変わってくると。
とりあえず簡単なパフォーマンステストをしてみたいと思います。
サンプルSQLは以下の通りです。
自前の環境で、条件を変えてテストしてみてください。

create table #tbl1 (
    id int,
    kbn nchar(10) ,
    point int
)

create table #tbl2(
    id int,
    kbn nchar(10) ,
    price int
)
--create index #idx2 on #tbl2(price);

declare @start_id as int
set @start_id=0
 
while @start_id < 100000
    begin
        print @start_id
        insert into #tbl1 values(@start_id, @start_id % 3, @start_id * Rand() * 10)
        insert into #tbl2 values(@start_id, @start_id % 3, Rand() * 1000)
        insert into #tbl2 values(@start_id, @start_id % 3, Rand() * 1000)
        insert into #tbl2 values(@start_id, @start_id % 3, Rand() * 1000)
        set @start_id = @start_id +1
    end

--select * from #tbl1
--select * from #tbl2 where price >= 900
--ここからテスト

/* EXISTS 句でパフォーマンステスト */
print CONVERT(VARCHAR, GETDATE(), 114) 
select * from #tbl1 t1
 where EXISTS (
  select * from #tbl2 t2
	where t1.id= t2.id     --レコードのリンク
	and t2.price >= 900
 )
print CONVERT(VARCHAR, GETDATE(), 114) 


/* IN 句でパフォーマンステスト */
print CONVERT(VARCHAR, GETDATE(), 114) 

select * from #tbl1 t1
 where t1.id IN (
  select t2.id from #tbl2 t2
   where t2.price >= 900
 )

print CONVERT(VARCHAR, GETDATE(), 114) 


/* join 句でパフォーマンステスト */
print CONVERT(VARCHAR, GETDATE(), 114) 

select distinct t1.* from #tbl1 t1
inner join #tbl2 t2
on t1.id=t2.id
where t2.price >= 900

print CONVERT(VARCHAR, GETDATE(), 114) 

/* drop table */
/*
drop table #tbl1
drop table #tbl2
*/

ここでまず、INDEX無のEXISTS,IN,INNER JOINのそれぞれの実行プランを確認してみます。
EXISTS句

EXISTS句-パターン1INDEX無の実行プラン

EXISTS句-パターン1INDEX無の実行プラン


クエリコストがバッチ相対で9%になっています。

IN句

IN句-パターン1INDEX無の実行プラン

IN句-パターン1INDEX無の実行プラン


EXISTS句と同じくクエリコストがバッチ相対で9%になっています。

INNER JOIN句

INNER JOIN句-パターン1INDEX無の実行プラン

INNER JOIN句-パターン1INDEX無の実行プラン


クエリコストがバッチ相対で25%になっています。

実行して試してみます。
ただし一度メモリに読み込まれてしまうと、最適化された状態で動くことになるので、実際のパフォーマンスとはかなり異なります。
もし、少しでも正確に行いたいのなら、テーブルを毎回作り直して、初回で比較する方が良いと思われます。
今回はメモリに展開したうえでのパフォーマンス比較を行っています。各々2回図ります。

参考:ど初回、EXISTS句
開始:07:52:12:613
終了:07:52:14:300
結果:1687ms

—–ここから

初回:EXISTS句
開始:08:01:44:583
終了:08:01:46:287
結果:1704ms

初回:IN句
開始:07:58:20:097
終了:07:58:21:690
結果:1687ms

初回:INNER JOIN句
開始:08:07:10:097
終了:08:07:12:363
結果:2266ms

2回:EXISTS句
開始:08:08:32:600
終了:08:08:33:927
結果:1327ms

2回:IN句
開始:08:05:31:440
終了:08:05:33:160
結果:1720ms

2回:INNER JOIN句
開始:08:03:56:490
終了:08:03:57:973
結果:1483ms

なんとも締まらない結果になってしまいました。
10万件では少なかったかな。。。
とりあえず、次はpriceにIndexを貼ってやってみます。
でも実行プランも変わらなかったので、priceにIndexを貼っても効果ないようです。
どこにどう貼れば効果あるのかな・・・
ちなみに、Indexを貼るのに10秒かかりました。

初回:EXISTS句
開始:08:13:15:660
終了:08:13:17:647
結果:1987ms

初回:IN句
開始:08:15:48:677
終了:08:15:50:520
結果:1843ms

初回:INNER JOIN句
開始:08:14:47:300
終了:08:14:49:130
結果:1830ms

2回:EXISTS句
開始:08:16:37:147
終了:08:16:38:270
結果:1123ms

2回:IN句
開始:08:18:28:660
終了:08:18:29:833
結果:1173ms

2回:INNER JOIN句
開始:08:17:32:973
終了:08:17:34:647
結果:1674ms

Indexを貼っても有効に機能しなかったので結果は同じでしたね。
簡単なテーブルではパフォーマンスに差も出ないようですね。
じっくり検討すれば早くなる可能性もありますが、とりあえず簡易テストの結果、それほどの差は出ないということでした。

以上、簡単なサンプルでの説明でしたが、SQL SERVER EXISTS、NOT EXISTS についてでした。

SQL SERVER 日付の加算、減算

SQL SERVER

日付の加算、減算

SQL Serverは、DATEADD関数を使って日付型の値に日時を加算、減算する事ができます。

構文:DATEADD(element, value, target)

elementには日付要素を記述します。
日付要素は以下のようなものがあります。
year(yy or yyyy) : 年
quarter(qq or q) : 四半期
month(mm or m) : 月
dayofyear(dy or y) : 年始からの日数
day(dd, d) : 日
week(wk, w) : 週
weekday(dw) : 曜日
hour(hh) : 時
minute(mi or n) : 分
second(ss) : 秒
millisecond(ms, s) : ミリ秒

valueには加算、減算した値を設定します。
targetには、基準となる日付型の値を設定します。

日付の加算

日の加算をしたい場合は、elementに「day」、valueに「加算したい日数」、targetに基準日を設定します。

例えば、現在から5日後の日付が欲しい場合は、
DATEADD(day, 5, getdate())
で取得できます。

例えば、現在から12時間後の日付が欲しい場合は、
DATEADD(hour, 12, getdate())
で取得できます。

日付の減算

日の減算をしたい場合は、elementに「day」、valueに「減算したい日数」、targetに基準日を設定します。
減算したい日数は、マイナスで指定します。
1日前なら-1、2日前なら-2

例えば、現在から5日前の日付が欲しい場合は、
DATEADD(day, -5, getdate())
で取得できます。

例えば、現在から12時間前の日付が欲しい場合は、
DATEADD(hour, -12, getdate())
で取得できます。

上の説明でほぼ理解できたと思いますが、一応例を載せておきます。

declare @d datetime
declare @d2 datetime
declare @d3 datetime
declare @d4 datetime

print '現在時刻をセット'
set @d=getdate()
print @d
print ''

print '10時間後を指定'
set @d2=dateadd(hour,10,@d)
print @d2
print ''

print '10日時間を指定'
set @d2=dateadd(hour,-10,@d)
print @d2
print ''

print '10日後を指定'
set @d2=dateadd(day,10,@d)
print @d2
print ''

print '10日前を指定'
set @d2=dateadd(day,-10,@d)
print @d2
print ''

print '簡易な計算方法----------'
print '10日後を指定'
set @d3=@d+10
print @d3
print ''

print '10日前を指定'
set @d3=@d-10
print @d3
print ''

print '簡易な計算方法で少数点を使うと----------'
print '0.001日後を指定'
set @d4=@d+0.001
print @d4
print @d4-@d
print convert(nvarchar(36), @d4-@d,21)
declare @dec decimal(11,6)
set @dec=3600*24/1000
print @dec/60

上の例の中に簡易な計算方法も載せておきました。
基本は日単位の計算なので単純な加算、減算なら+10とか-10で計算してくれます。
また、12時間とか6時間という切りの良い時間なら、0.5や0.25と言った指定でできます。

この辺りは好みもありますが、ちゃんとDATEADDを使った方が面倒にならない気もします。
以上、SQL SERVER 日付の加算、減算の開発備忘録でした。

SQL SERVER 照合順序

SQL SERVER

照合順序

照合順序とは

SQL Server の照合順序とは、データをソートする際の文字の大小関係を比較する基準のことを照合順序 (collation) と呼んでいます。
例えば、「川」と「海」ではどちらが早く取得・表示されるのか、「あ」「ア」「ア」をソートする時どのようにソートされたいのかといった、文字の大小関係を決めているのが照合順序です。

また、ソートだけでなく検索でマッチするかどうかも照合順序で決まります。
「ア」「ア」を同じとみなすかどうかとか。

また、データのインデックス作成時にも照合順序は使われます。
インデックスの項目がキャラクタ( char, nchar, varchar, nvarchar, text, ntext)などの場合、項目の値順にインデックス行を並び替えるために使われます。

仕事で決まった順にソートして表示したり、該当するデータを照合抽出することは当たり前のことで、そのソートや照合する際に照合順序 (文字の大小関係) はとても重要な要素です。

で、照合順序の決め方なのですが、なんか複雑で良くわからないのです・・・

日本語照合順序の違い

ソート順、文字の大小関係の定義が違う。
辞書順に並び変えた場合の並び順が、使用している日本語辞書の順になることを表しています

Japanese_XJIS
Japanese Unicode 2.0(80), Unicode 3.2(90)に対応
Unicode 5.0(100)に対応
Japanese_Bushu_Kakusu 部首画数順、Unicode 5.0(100)に対応
Japanese_Unicode SQL Server 7.0 との互換性のためだけに残されています

単純に考えると、最新版のJapanese_XJISを使えば良いのかと思います。

日本語照合のバージョン

上で日本語辞書を指定し、次はコードのバージョンを指定します。

記載なし(80) SQL Server 2000, Unicode 2.0
90 SQL Server 2005, Unicode 3.2
100 SQL Server 2008, Unicode 5.0

要は、Japanese_100の指定はできないってことですね。
Japanese_XJISは100を指定しなくてもデフォルトで100なのかな?

日本語照合オプション

C : 大文字と小文字の区別
 CS(CaseSensitivity) 大文字と小文字を区別する
 CI(CaseInsensitive) 大文字と小文字を区別しない

A:アクセントの区別
 AS(AccentSensitivity) アクセントを区別する
 AI(AccentInsensitive) アクセントを区別しない
 ※日本語の場合は清音と濁音・半濁音の区別。区別しない場合は AI 。

K:ひらがなとカタカナの区別
 KS(KanatypeSensitive) ひらがなとカタカナを区別する。
 ※区別しない場合はオプションを指定しない。

W:1 バイト文字と 2 バイト文字の区別
 WS(WidthSensitivity) 1 バイト文字と 2 バイト文字を区別する。(w と w 等)
 ※区別しない場合はオプションを指定しない。

SC:Supplementary Character(補助文字)を認識するかどうかの区別
 オプションをしてした場合は、Supplementary Character(補助文字)を認識する。
 ※文字数を返す LEN 等の動作に影響する。
 ※区別しない場合はオプションを指定しない。

日本語照合オプション2

BIN:バイナリ並べ替え
 SQL Server 2000 以前の古いバージョンとの互換目的。
 (最初の一文字のみコードポイントで比較、以降はバイトごとに比較)

BIN2:バイナリ並べ替え
  すべての文字をコードポイントで比較する。

ざっと日本語を取り扱う場合の照合順序を抜粋して書いてみました。
人名や住所なのでソートや抽出を行う時に関与してきますので、抽出がおかしいときは見直してください。

以上、「SQL SERVER 照合順序」についての備忘録でした。

【SQL SERVER】 一時テーブルにPRIMARY KEYやINDEXを付ける

一時テーブルにPRIMARY KEYやINDEXを付ける開発備忘録です。
一時テーブルの備忘録を書いたときに、「一時テーブルには、PRIMARY KEYを付けたり、INDEXを付けたりもできます。」と書きましたので、その付け方を備忘録しておきます。

まずは、一時テーブルを作成します。

create table #tmpTable
(
	a_id int NOT NULL
	,b_cd nvarchar(2) NULL
	,c_val nvarchar(10) NULL
)

次に、PRIMARY KEYを設定します。
a_idをPRIMARY KEYとして設定します。

alter table #tmpTable ADD PRIMARY KEY CLUSTERED 
(
	a_id asc
)

もちろん、一度に作ってしまっても問題ありません。

create table #tmpTable
(
	a_id int NOT NULL
	, b_cd nvarchar(2) NULL
	, c_val nvarchar(10) NULL
	,CONSTRAINT tmpTable_PK PRIMARY KEY CLUSTERED 
    (
      a_id asc
    )
)

次に、INDEXを設定します。
もちろん、PRIMARY KEYを設定しなくてもINDEXは設定できるます。
INDEX名は適当に。
create NONCLUSTERED INDEX tmpTable_INDEX_1 ON #tmpTable
(
a_id asc
,b_cd asc
)

以上、一時テーブルにPRIMARY KEYやINDEXを付ける開発備忘録でした。

【SQL SERVER】 一時テーブル

一時テーブルについての開発備忘録です。

データ処理を行っている時に、ちょっとした途中加工データを置いて置きたい場合があります。
でも、メモリに置くには大きすぎてという場合も有るし、テーブルに格納するのも排他制御や使用後の削除などの手間も有るのでテーブルは使いたくない。。。
通常のテーブルを使う場合は、固有のプロセスIDを負荷した列を設けて他のプロセスから切り離すなどをしないといけません。
そんな時に使うのが、「一時テーブル」です。一時という名前はついていますが、テーブルアクセスしてSQLで処理できるので楽にデータが扱えます。

一時テーブルは非常に使い勝手良く、ほぼ通常のテーブルと変わらぬ動きをします。
一時テーブルには、PRIMARY KEYを付けたり、Indexを付けたりもできます。
一時テーブルは、tempdb に格納される点と使用されなくなると自動的に削除されるという点を除いて、通常のテーブルと同じです。

一時テーブルには、ローカル一時テーブルとグローバル一時テーブルの2種類があります。
この2種類の一時テーブルでは、名前(#1つか2つかの違い)と表示とアクセス範囲が異なります。

ローカル一時テーブル名の先頭には、番号記号 (#) が1つ付いています。
このテーブルは、作成したユーザーの現在のセッション接続でのみ表示され、このユーザーが SQL Server のインスタンスから切断すると削除されます。
ローカル一時テーブルは、事前に現在のセッション内だけという制約も有り、最初から排他されているので排他処理等の手続きも不要です。
execSQLのように動的にSQLを動かす場合以外は、通常こちらのローカル一時テーブルを使用します。

create table #tmpTable
(
	a_id int NOT NULL
	, b_cd nvarchar(2) NULL
	, c_val nvarchar(10) NULL
)

グローバル一時テーブル名の先頭には、番号記号が2つ (##) 付いています。
このテーブルは、作成されるとすべてのユーザーに表示され、このテーブルを参照するすべてのユーザーが SQL Server のインスタンスから切断すると削除されます。
全てのユーザが使用可能になりますので、予めどのような状態のデータかがはっきりしている必要があります。
execSQLのように同一ユーザが動的にSQLを他のセッションで動かす場合のような単純な使い方であれば使い勝手も良いのですが、それ以外は通常のテーブル使用とあまり変わりません。

create table ##tmpTable
(
	a_id int NOT NULL
	, b_cd nvarchar(2) NULL
	, c_val nvarchar(10) NULL
)

しかし多くの場合、一時テーブルを使用する代わりに、table データ型の変数を使用することができます。table 変数については、変数を使用したテーブルの利用方法を見てください。

以上、一時テーブルについての開発備忘録でした。

【SQL SERVER】 四捨五入・切り捨て・切り上げについて

四捨五入・切り捨て・切り上げについての備忘録。

SQL SERVERのSQLを書いているとたまに「四捨五入」・「切り捨て」・「切り上げ」を取得時にしたい時があります。
本来生データの精度は、できる限る取得できる最大限の精度で保存したいものですが、使用する時にそこまで必要ない場合などありますし。

で、四捨五入・切り捨て・切り上げに使える関数ですが、
四捨五入には 「ROUND」
切り捨てには、「CEILING」
切り上げには、「FLOOR 」を使います。

ROUNDは、以下のようにでパラメターを3つ使います。
ROUND(四捨五入したい数値, 有効桁数[, 0 – 四捨五入、0 以外- 切捨て。 オプショナルでデフォルトは 0 ])
有効桁数は(0が1の位、1が小数点第1位、2が小数点第2位、3が小数点第3位、マイナス方向は-1であれば10の位、-2であれば100の位-3であれば1,000の位のようになります。

例えば、以下のようになります。

SELECT ROUND(1234.1234, -3) --> 1000.0000
SELECT ROUND(1234.1234, -2) --> 1200.0000
SELECT ROUND(1234.1234, -1) --> 1230.0000
SELECT ROUND(1234.1234, 0) --> 1234.0000
SELECT ROUND(1234.1234, 1) --> 1234.1000
SELECT ROUND(1234.1234, 2) --> 1234.1200
SELECT ROUND(1234.1234, 3) --> 1234.1230

3つめのパラメータを設定すると

SELECT ROUND(1555.1234, -3,0) --> 2000.0000
SELECT ROUND(1555.1234, -3,1) --> 1000.0000

FLOORとCEILINGはパラメターを1つだけで、そのまま整数値に切り捨て・切り上げしてくれます。

切り捨て(FLOOR)
例えば、以下のようになります。

SELECT FLOOR(1234.1234) --> 1234
SELECT FLOOR(1234.5678) --> 1234

FLOORとCEILINGは整数値に切り捨て・切り上げするので、小数点で切り上げ切り捨てを使うには、少し工夫が必要です。
まぁ、工夫と言っても桁を合わせてあげるだけですが。
例えば、以下のようになります。

SELECT FLOOR((1234.1234 * 10)) / 10 --> 1234.100000
SELECT FLOOR((1234.5678 * 100)) / 100 --> 1234.560000

切り上げ(CEILING)

SELECT CEILING(1234.1234) --> 1235
SELECT CEILING(1234.5678) --> 1235

SELECT CEILING(1234.1234 * 10) / 10 --> 1234.200000
SELECT CEILING(1234.5678 * 100) / 100 --> 1234.570000

以上、四捨五入・切り捨て・切り上げについての開発備忘録でした。

【SQL SERVER】 Pivot関数の使い方

今日はSQL SERVERのPivotに付いて使い方例を開発備忘録しておきます。

PivotはEXCELで良く集計に使われる関数です。
一般的にデータは正規化されて項目重複のない状態で保存されます。
例えば、売り上げは、日時、店舗CD、商品CD、個数、他と、店舗マスタ、商品マスターのような状態で保存されます。
人が見やすいデータは正規化されたデータと異なり、
例えば、同じ売り上げでも、日付、店舗名、商品名、個数、合計価格となります。

上の販売データの羅列を見せられても、人は売上状況を把握しづらいものです。
もっと言うなら、「森全体 ⇒ 森の一部 ⇒ 木 ⇒ 幹 ⇒ 枝 ⇒ 葉」のようにマクロなデータからミクロに向かう方が把握分析しやすいと言われています。

Pivotは個々の葉のレコードを寄り集めて、枝や幹、木、森の一部のような集計したデータにしてくれる関数です。
下のSQL例は、日付を行に商品を列に金額を合計した表をPivot関数を利用して返すようにしたものです。
また、PivotのIN句の中ではサブクエリが使用できないようなので、動的にSQLを作ってexecSQLで実行しています。
変数テーブルを使用しているため、execSQLでちょっと面倒になっていますが、本来はこんなことにならないので。。。勘弁してください。

declare @tbl1 table(s_date datetime, goods_cd nvarchar(9), price int)
insert into @tbl1 (s_date,goods_cd,price) values (GETDATE()-1, '001', 60)
insert into @tbl1 (s_date,goods_cd,price) values (GETDATE()-1, '010', 40)
insert into @tbl1 (s_date,goods_cd,price) values (GETDATE()-1, '020', 20)
insert into @tbl1 (s_date,goods_cd,price) values (GETDATE()-1, '030', 100)
insert into @tbl1 (s_date,goods_cd,price) values (GETDATE()-1, '001', 30)

insert into @tbl1 (s_date,goods_cd,price) values (GETDATE(), '001', 100)
insert into @tbl1 (s_date,goods_cd,price) values (GETDATE(), '010', 150)
insert into @tbl1 (s_date,goods_cd,price) values (GETDATE(), '020', 180)
insert into @tbl1 (s_date,goods_cd,price) values (GETDATE(), '020', 200)
insert into @tbl1 (s_date,goods_cd,price) values (GETDATE(), '030', 70)
 
insert into @tbl1 (s_date,goods_cd,price) values (GETDATE()+1, '001', 80)
insert into @tbl1 (s_date,goods_cd,price) values (GETDATE()+1, '010', 120)
insert into @tbl1 (s_date,goods_cd,price) values (GETDATE()+1, '030', 60)

select * from @tbl1

/*pivot*/
select * from (select convert(VARCHAR, s_date, 111 ) as s_date, goods_cd, price from @tbl1) S1 pivot (sum([price]) for [goods_cd] in ([001], [010], [020])) T1 

--IN句を動的に作ろうとするとエラーになる
--select * from (select convert(VARCHAR, s_date, 111 ) as s_date, goods_cd, price from @tbl1) S1 pivot (sum([price]) for [goods_cd] in (select distinct goods_cd from @tbl1)) as T 


--そこで変数内にSQLを書き、実行する。
--IN句を作り、@goods_listに格納する
declare @goods_list nvarchar(128)
select @goods_list=replace(REPLACE((SELECT distinct goods_cd AS [kugiri] 
                   from	@tbl1
                   for xml path ('')), '</kugiri>', '],'), '<kugiri>','[') 
set @goods_list=substring(@goods_list,0,len(@goods_list))

--実行SQLクエリ作成
--変数テーブルが読めないため、ここにも追加(本来は要らない)
declare @sql nvarchar(2048)
set @sql =
    '
	declare @tbl1 table(s_date datetime, goods_cd nvarchar(9), price int)
	insert into @tbl1 (s_date,goods_cd,price) values (GETDATE()-1, ''001'', 60)
	insert into @tbl1 (s_date,goods_cd,price) values (GETDATE()-1, ''010'', 40)
	insert into @tbl1 (s_date,goods_cd,price) values (GETDATE()-1, ''020'', 20)
	insert into @tbl1 (s_date,goods_cd,price) values (GETDATE()-1, ''030'', 100)
	insert into @tbl1 (s_date,goods_cd,price) values (GETDATE()-1, ''001'', 30)

	insert into @tbl1 (s_date,goods_cd,price) values (GETDATE(), ''001'', 100)
	insert into @tbl1 (s_date,goods_cd,price) values (GETDATE(), ''010'', 150)
	insert into @tbl1 (s_date,goods_cd,price) values (GETDATE(), ''020'', 180)
	insert into @tbl1 (s_date,goods_cd,price) values (GETDATE(), ''020'', 200)
	insert into @tbl1 (s_date,goods_cd,price) values (GETDATE(), ''030'', 70)
 
	insert into @tbl1 (s_date,goods_cd,price) values (GETDATE()+1, ''001'', 80)
	insert into @tbl1 (s_date,goods_cd,price) values (GETDATE()+1, ''010'', 120)
	insert into @tbl1 (s_date,goods_cd,price) values (GETDATE()+1, ''030'', 60)

	select * from (select convert(VARCHAR, s_date, 111 ) as s_date, goods_cd, price from @tbl1) S1 pivot (sum([price]) for [goods_cd] in (' + @goods_list + ')) T1
	'
 
-- SQLクエリの実行
exec sp_executesql @sql;

以上、今日はSQL SERVERのPivotに付いて使い方例を開発備忘録でした。

【SQL SERVER】 日付の年月日だけを取り出す(convert)

SQL SERVERで日付の年月日だけを取り出す(convert)開発備忘録です。

比較的よく使う変換ですので、簡単にメモ書きしておきます。
SELECT CONVERT ( VARCHAR, GETDATE(), 111 )

111の部分は以下の通り。
111なので、日本 yyyy/mm/ddで取得できます。
11か111以外はほぼ使わないと思いますが、たまに使うことも有るかもしれないので、一式載せておきます。

2 桁の年 (yy) 年を 4 桁で表現 (yyyy) 標準 入力/出力**
0 または 100 (*) 既定値 mon dd yyyy hh:mi AM (または PM)
1 101 米国 mm/dd/yyyy
2 102 ANSI yy.mm.dd
3 103 英国/フランス dd/mm/yy
4 104 ドイツ dd.mm.yy
5 105 イタリア dd-mm-yy
6 106 dd mon yy
7 107 Mon dd, yy
8 108 hh:mm:ss
9 または 109 (*) 既定値 + ミリ秒 mon dd yyyy hh:mi:ss:mmm AM (または PM)
10 110 米国 mm-dd-yy
11 111 日本 yy/mm/dd
12 112 ISO Yymmdd
13 または 113 (*) ヨーロッパ: 既定値 + ミリ秒 dd mon yyyy hh:mm:ss:mmm (24h)
14 114 hh:mi:ss:mmm (24h)
20 または 120 (*) ODBC 標準 yyyy-mm-dd hh:mi:ss (24h)
21 または 121 (*) ODBC 標準 (ミリ秒を含む) yyyy-mm-dd hh:mi:ss.mmm (24h)
126(***) ISO8601 yyyy-mm-ddThh:mm:ss.mmm (スペースなし)
130* イスラム**** dd mon yyyy hh:mi:ss:mmm AM
131* イスラム**** dd/mm/yy hh:mi:ss:mmm AM

以上、SQL SERVERで日付の年月日だけを取り出す(convert)開発備忘録でした。

【SQL SERVER】 CASE文の使い方

CASE文を使って結果を分岐させて取得するSQL文の備忘録です。

CASE文は、一連の条件を評価して、考えられる結果式のうちの 1 つを返します。
CASE 式には 2 つの形式があります。
 単純 CASE 式では、1 つの式を一連の単純式と比較して結果を決定します。
 検索 CASE 式では、一連のブール式を評価して結果を判定します。
どちらの形式も、ELSE 引数 (省略可) をサポートしています。
CASE は、有効な式を使用できる任意のステートメントや句で使用できます。 たとえば、SELECT、UPDATE、DELETE、SET などのステートメントや、select_list、IN、WHERE、ORDER BY、HAVING などの句で使用できます。

簡単な例を記述します。

declare @tbl1 table(a_cd char(9), b_kbn char(1), c_kbn int)
insert into @tbl1 (a_cd,b_kbn,c_kbn) values ('000000001','1',0)
insert into @tbl1 (a_cd,b_kbn,c_kbn) values ('000000010','1',1)
insert into @tbl1 (a_cd,b_kbn,c_kbn) values ('000000020','2',1)
insert into @tbl1 (a_cd,b_kbn,c_kbn) values ('000000030','2',1)
 
select * from @tbl1

--例1
select a_cd,
    case b_kbn when '1' then 'OK' when '2' then 'NG' else '-' end as 判定
from
    @tbl1

--例2
select a_cd,
	case
		when b_kbn = '1' OR c_kbn = '0' then 'OK1'
		when b_kbn = '1' OR c_kbn = '1' then 'OK2'
		when b_kbn = '2' OR c_kbn = '0' then 'NG1'
		when b_kbn = '2' OR c_kbn = '1' then 'NG2'
		else '-' 
	end as 判定
from @tbl1

以上、CASE文を使って結果を分岐させて取得するSQL文の備忘録でした。

【SQL SERVER】 replace 文字列置き換え

データベースを管理していると、いろいろな理由でデータを読み替えて取得したいことがあります。
例えば、プログラム決め打ちで登録したデータが間違っていて、登録ミスが発生したいた場合には一括でデータの文字列を変えたいです。
また、安易に考えたタグ名などを変えたい時などもたまにあります。

そんな時、SQL SERVERであれば、replace関数を使います。
使い方は至って簡単です。

replace([項目],’置き換え前の文字’,’置き換え後の文字’)

例)select replace(‘abcde’,’abc’,’cba’)
結果)’cbade’

テーブルのデータを一括で置き換えたい時は、
update tableA
set [項目]=replace([項目],’前の文字’,’後の文字’)
from tableA
where 条件

などとすれば一括で文字列置き換えができます。
以上、replace 文字列置き換えの開発備忘録でした。