【SQL SERVER】ストアドのselect結果をテーブルに登録する方法

下記のようにします。予めテーブル定義をしておくこと。

◻︎構文
insert into table exec procedure

【SQLSERVER】top句を使用したデータ取得件数の指定方法

抽出データの中から件数を指定して取得する方法。

◻︎ランダム5件
select top 5 * from tbl

◻︎昇順に5件
select top 5 * from tbl order by col

◻︎降順に5件
select top 5 * from tbl order by col desc

【SQLSERVER】SQLで数値の連番を取得する方法

テーブルを増やしていけばいくらでも連番の生成が可能。
テーブルの結合条件を指定せずに各テーブルの結果を積算し、重み付けして集計する。

◆1から999までの数値
select X.col + XX.col * 10 + XXX.col * 100
   from (
             select 1 union select 2 ・・・
            ) X
           ,(
             select 1 union select 2 ・・・
            ) XX
          , (
             select 1 union select 2 ・・・
            ) XXX
order by X.col + XX.col * 10 + XXX.col * 100

【SQLSERVER】Or指定をUnion化してインデックスを有効活用する方法

col1、col2にインデックスが存在する場合に有効。

◆改善前
select *
   from XXX
  where col1 = AAA
       or col2 = BBB

◆改善後
select *
   from (
             select * from XXX where col1 = AAA Union
             select * from XXX where col2 = BBB
            ) XXX

【SQLSERVER】集計関数 min maxを利用した重複チェックの方法

特定項目に対して重複データが存在するかのチェックなど。

◆データ

col1  col2

A      りんご

A　  りんご

B       りんご

C      りんご

C       みかん

select col1 , min(col2) ,max(col2)

 group by col1

 having min(col2) <> max(col2)

◆結果

col1   min    max

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

C       みかん　りんご

【SQLSERVER】複数列を1行にして集計する方法

case文を利用して集計したい項目名に合致するデータのみ集計対象とし、合致しない場合は0を定義して集計対象外とする。

◆データ
 col1  col2
 ---------------
 東京都　3
 千葉        1
 千葉        3
 千葉        2
 神奈川    5


select  sum(case when col1 = '東京' then col2 else 0 end) 東京集計
          ,sum(case when col1 = '千葉' then col2 else 0 end) 千葉集計
          ,sum(case when col1 = '神奈川' then col2 else 0 end) 神奈川集計
   from xxx

◆結果
　東京集計 千葉集計 神奈川集計
　-------------------------------
    3               6               5

【SQLSERVER】重複を取り除いた件数を取得したい

◆データ
col1 col2
--------------
東京　A
東京　B
埼玉　A
千葉　A
千葉　A

茨城　A

◆サンプル

select col1 ,count(distinct col1)  ,count(col1)

  from XXX

group by col1

◆結果

col1 --

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

東京 1 2

埼玉 1 1

千葉 1 2

茨城 1 1

集計関数の集計対象に対してDistinctを指定して重複を取り除く事ができる。

【SQLSERVER】関連性のない項目同士を同一行で抽出したい

関連性のない項目同士を同一行で抽出したい場合等。
Row_Numuberを使用して表示したい順に連番を設定し、連番で結合する。連番はPKとなる。

◆データXXX
 col1
 ------------
 A
 B
◆データXXX2
col2
------------
あ
い

◆サンプル
select XXX.col_key ,xxx.col1 ,xxx2.col1
   from (
             select row_number () over (order by col1) as col_key ,col1 from XXX
            ) XXX
inner join
            (
             select row_number () over (order by col1) as col_key ,col1 from XXX2
            ) XXX2
      on XXX.col_key = XXX2.col_key

◆結果
col_key col1 col2
---------------------
1  A あ
2  B い

# いずれか一方がないばあいも取得したい場合は結合条件をfull joinを指定する。

【SQLSERVER】取得データの特定項目毎に連番を設定する方法

下記例ではcol1毎に連番を１から設定している。(partition句を使用して連番をクリア)

◆データ例
　col1  col2
    -----------------
    東京　A
　東京　B
　東京　C
    埼玉　A
　埼玉　B
　埼玉　C

◆サンプルSQL

select  col1 ,col2 ,row_numer() over(partition by col1 order by col2)

   from XXX

◆結果

　col1  col2　row_number

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

    東京　A    1

　東京　B    2

　東京　C    3

    埼玉　A    1

　埼玉　B    2

　埼玉　C    3

【SQLSERVER】取得データに連番を設定する方法

selectで取得できることもできますが、条件に設定にも指定できるので、5番目以上のみ取得するなどの使い方ができる。

◆データ例
　col1
    ---------------
    a
    b
    c

◆サンプルsql(colの昇順に1から連番を設定)
select row_number () over (order by col1) as  ,col1
   from XXX

     row_numuber  col1
    ------------------------
    1      a
    2      b
    3      c


◆サンプルsql(colの降順に1から連番を設定)
select row_number () over (order by col1 desc) ,col1
   from XXX


     row_numuber  col1
    ------------------------
    3      a
    2      b
    1      c

【SQLSERVER】テーブル同士に存在しないデータの確認方法

例えばテーブルAにあるがテーブルBに存在しないデータの確認方法。

select *

  from XXX_A

 where not exists (

                             select 1 

                                from XXX_B

                              where 結合条件

                            )

【SQLSERVER】テーブルのコピーを簡単に作成する方法

更新等に一時的にテーブルをバックアップしたい場合に利用する。

◆バックアップ
select * into XXX_20130215 from XXX

◆内容確認
select * from XXX
select * from XXX_20130215

XXX_20130215 という名称のテーブルが作成されてバックアップされている。一時的に保存しておく場合に非常に便利です。

【SQLSERVER】Viewでのソート指定の注意点

SQLSERVER2000ではViewの定義内のソートは有効だったが、以降のVersionでは使えなくなっているので注意。

◆View定義
select * from XXX Order by col1,co2l

◆View使用時
Select * from vw_XXX Order by col1,col2

上記のようにView参照時に並べ替える必要がある。

【SQLSERVER】テーブルから出力したデータに任意の見出しをつけたい

サンプル
select xxx2.col2 ,xxx2.col2
   from (
              -- ヘッダー部
              select 1 as sortKey , "項目A" as col1 ,"項目B" as col2
              union
              -- データ部
              Select 2 , col1 , col2
                 from xxx
            ) xxx2
order by xxx.sortKey

◆結果イメージ
項目A 項目B
AAA    BBB
CCC     DDD


◆説明
Unionのヘッダー部を見出しの固定文字とし、データ部をテーブルからファイルに出力したい項目をとする。ソートの優先順位をヘッダー部　→　明細部となるように固定数値で指定して外側でOrder byする。

【SQLSERVER】テーブルのPKを表示する方法

sp_pkeys テーブル名
テーブルのpkが表示できる。

【SQLSERVER】テーブルの項目を表示する方法

sp_columns テーブル名
テーブルの名称や型が表示できるので便利。

【SQLSERVER】 In句をExists句に置き換える

一般的にExists句のが高速、そのうえカスタマイズ性が高いのでおすすめ。
下記は簡単例です。１テーブルなのであまり意味はないですが。

◆In句
select *
   from xxx
 where col in ('a' ,'b')

◆Exists句
select *
   from xxx
 where Exists (
                        select 1
                           from xxx
                         where col in ('a' ,'b')
                       )

【SQLSERVER】OR条件に対するインデックスの有効活用

Col1にインデックスを設定されているが、Or指定のため活用されない場合は、チューニング後のようにUnion句で条件を指定すると有効活用されて検索結果が高速化される。

◆チューニング前
select *
   from xxx
 where col1 in ('a' ,'b')

◆チューニング後
select *
   from (
             select * from xxx where col1 = 'a' union
             select * from xxx where col2 = 'b'
            )