□技術メモ - SQL

※管理人の個人的な技術メモです。このページの内容を実行した結果について
管理人はいかなる責任も負いかねますのでご自身の責任でお試しください。

-----------------------------------------------------------

○小ネタ

(SqlServer)
union all        重なるデータを削除しない。通常のunionは同じデータは削除される。

○Havingの構文

Select … From テーブル名 Group By … Having 集計関数の式

・(例)自治体数が30以上の県名を取得する場合
Select 県名 , Count(*) As 県別の自治体数 From tbl自治体一覧 Group By 県コード Having Count(*) > 30;

○Null→固定文字列への変換

(SqlServer)
IsNull(項目の値 , "")

(Oracle)
NVL(項目の値 , " ")　　または
Decode(項目の値 , 変換前1 , 変換後1 , 変換前2 , 変換後2 …  , ELSEの場合の変換後の値)　を使用する

○テーブル間で、(名称1 + 名称2) に差分があることの判定
ただし、NULL←→空文字は差がないと判定する。
'AAA' + 'BBB' と 'AAABB' + 'B' は差分ありと判定する。

(SqlServer)
Select c.PK
From
(Select TBL1.PK as PK, 
IsNull(a.名称1, '') as A1, IsNull(a.名称2, '') as A2, 
IsNull(b.名称1, '') as B1, IsNull(b.名称2, '') as B2 
From TBL1 as a, TBL2 as b
Where TBL1.PK = TBL2.PK) as c
Where c.A1<>c.B1 Or c.A2<>c.B2;
-- 'セパレータに該当する文字列が使われていないことが分かっている場合は以下でもよい。
Where (c.A1 + '//separator//' + c.A2)<>(c.B1 + '//separator//' + c.B2)

○日付・時刻フォーマット

(SqlServer)
select convert(varchar,getdate(),111)    --yyyy/MM/dd
select convert(varchar,getdate(),112)    --yyyyMMdd
select convert(varchar,getdate(),121)    --yyyy-MM-dd hh:mm:ss.fff
select getdate()                         --表示は上記と同じ
select convert(varchar,getdate(),108)    --hh:mm:ss

--備考
SQLのinsert文でmillSecondまで指定した場合、丸めが発生して、
指定した値そのままにはならない。
0.0, 0.3, 0.7の近くに丸められる。（おそらく、0.000..., 0.333..., 0.666 に丸めている ）
例
insert into xxTbl(... , UpdateTime, UpdateDate)
values           (... , getdate(),  '2014-11-21 18:13:45.221')

UpdateTime, UpdateDate は datetime型
実行結果は、UpdateDate の値は '2014-11-21 18:13:45.220'になる。

○Joinの仕方いろいろ

(SqlServer)
left join        左側全部と右側一致するレコード　　Oracleで右側に(+)をつけるのと同じ
right join       右側全部と左側一致するレコード　　Oracleで左側に(+)をつけるのと同じ
inner join       左右一致
full outer join  左右それぞれ全て取得
cross join       全ての組み合わせ

○PartitionとROW_NUMBER()（SqlServer）

・区分ごとに通し番号を振る
Select ROW_COUNT_BIG() - ROW_NUMBER() Over (Partition by xx区分 order by xx番号) as number From テーブル名
・ROW_COUNT_BIG()は区分別のレコード数を取得するので上記の例は逆順の通し番号になる。
・xx区分を 1 に設定すると、全体に対する通し番号になる。