SQLServerで連番データ

SQLServerで連番データ

#####「再帰SQL」を使って指定された値までのデータを取得

0～9の連番.sql

WITH serial AS ( 
  SELECT 0 AS N -- 開始値
  UNION ALL 
  SELECT N + 1 FROM serial 
  WHERE serial.N < 9 -- 最大値
) 
SELECT N FROM serial

• 結果

N
0
1
2
3
4
5
6
7
8
9

再帰は多すぎるとダメ（規定値では100回まで？）

MAXRECURSIONで設定できるらしい
最大再帰数「MAXRECURSION」
設定できる値は「0」から「32,767」までで、「0」は上限なし
規定値は「100」

0～1000の連番.sql

WITH serial AS ( 
  SELECT 0 AS N -- 開始値
  UNION ALL 
  SELECT N + 1 FROM serial 
  WHERE serial.N < 1000 -- 最大値
) 
SELECT N FROM serial
OPTION ( MAXRECURSION 1000 )

大量データ

大量データの場合、再帰では遅くなる
100以上必要なときはこんな感じにするといいかも
再帰だけでやるより、こっちのほうが150～200倍早かった

100万件.sql

WITH serial AS ( 
  SELECT 1 AS N 
  UNION ALL 
  SELECT N + 1 
  FROM serial 
  WHERE serial.N < 10
) 
,K AS (
SELECT 1 AS N 
FROM serial A, serial B, serial C  -- 10×10×10で1000件
)
SELECT ROW_NUMBER() OVER (ORDER BY A.N) AS N 
FROM K K1, K K2  -- 1000×1000で100万件

PIVOTも絡めて応用

九九の表を作ってみた

九九の表.sql

WITH serial AS ( 
  SELECT 1 AS N 
  UNION ALL 
  SELECT N + 1 FROM serial 
  WHERE serial.N < 9
) 
,base AS (
SELECT x.N AS X , y.N AS Y ,(x.N * y.N) as A
FROM serial x,serial y
)
SELECT
Y AS [X/Y], 
[1], [2], [3], [4], [5], [6], [7], [8], [9]
FROM base
PIVOT (SUM([A]) FOR [X] IN ([1], [2], [3], [4], [5], [6], [7], [8], [9])) AS PV

再帰を使わない方法

システムテーブルを利用して内容を使用しないで、
ROW_NUMBER()で連番を作成

システムテーブルの行数までの範囲ならこれでいいかも
大量データの場合はシステムテーブルを複数重ねるとできるが、列が複数ある分、再帰を使ってのほうが速そう

システムテーブルを利用.sql

SELECT TOP 1000
  ROW_NUMBER() OVER (ORDER BY A.object_id) AS N  --  ORDER BY にはプライマリキーを指定
FROM
  sys.all_columns A  -- 件数が多くて速そうなシステムテーブルあるいはビュー