[SQL Server]CTE式の再帰呼び出しを利用して連番テーブルを作成する

[スポンサードリンク]

「１・２・３・４・・・・・１０」など、連番が入った一時テーブルを作成する。

概要

「ROW_NUMBER()」関数は、レコードがあるテーブルに対して行番号を振る。
これに対して、他のカラムがない連番だけのテーブルを作るにはCTE式の再帰呼び出しを使用する。

ROW_NUMBER関数を使用する場合は↓↓↓こちら↓↓↓
SQLServer　SELECT文で連番を振る（項目ごとの連番・通し連番）

０埋めしない連番

WITH句で列名を定義しない方法

-- 列名はクエリのAS句で指定する
WITH seq_tbl AS
(
    SELECT
        1 AS idx
    UNION ALL
        SELECT
            seq_tbl.idx + 1
        FROM
            seq_tbl
        WHERE
            seq_tbl.idx < 10
)
SELECT * FROM seq_tbl

-- 列名はクエリのAS句で指定する

WITH seq_tbl AS

(

SELECT

1 AS idx

UNION ALL

SELECT

seq_tbl.idx + 1

FROM

seq_tbl

WHERE

seq_tbl.idx < 10

)

SELECT * FROM seq_tbl

WITH句で列名を定義する方法

-- 列名はWITH句で指定する
WITH seq_tbl(idx) AS
(
    SELECT
        1
    UNION ALL
        SELECT
            seq_tbl.idx + 1
        FROM
            seq_tbl
        WHERE
            seq_tbl.idx < 10
)
SELECT * FROM seq_tbl

-- 列名はWITH句で指定する

WITH seq_tbl(idx) AS

(

SELECT

UNION ALL

SELECT

seq_tbl.idx + 1

FROM

seq_tbl

WHERE

seq_tbl.idx < 10

)

SELECT * FROM seq_tbl

０埋めする連番

WITH句で列名を定義しない方法

-- 列名はクエリのAS句で指定する
WITH seq_tbl AS (
    --01～10までの連番を作成
    SELECT
        CAST('01' AS VARCHAR(2)) AS idx
    UNION ALL
    SELECT
        CAST(FORMAT(1 + idx, '00') AS VARCHAR(2))
    FROM seq_tbl
    WHERE idx < '10'
)
SELECT * FROM seq_tbl

-- 列名はクエリのAS句で指定する

WITH seq_tbl AS (

--01～10までの連番を作成

SELECT

CAST('01' AS VARCHAR(2)) AS idx

UNION ALL

SELECT

CAST(FORMAT(1 + idx, '00') AS VARCHAR(2))

FROM seq_tbl

WHERE idx < '10'

)

SELECT * FROM seq_tbl

WITH句で列名を定義する方法

-- 列名はWITH句で定義する
WITH seq_tbl (idx) AS (
    --01～10までの連番を作成
    SELECT
        CAST('01' AS VARCHAR(2))
    UNION ALL
    SELECT
        CAST(FORMAT(1 + idx, '00') AS VARCHAR(2))
    FROM seq_tbl
    WHERE idx < '10'
)
SELECT * FROM seq_tbl

-- 列名はWITH句で定義する

WITH seq_tbl (idx) AS (

--01～10までの連番を作成

SELECT

CAST('01' AS VARCHAR(2))

UNION ALL

SELECT

CAST(FORMAT(1 + idx, '00') AS VARCHAR(2))

FROM seq_tbl

WHERE idx < '10'

)

SELECT * FROM seq_tbl

０埋めの桁数を増やしたいとき

１．UNION ALLの上のクエリ部分の赤字のゼロを増やして、VARCHARの桁数も合わせる。
　　「CAST(‘001‘ AS VARCHAR(3))」
　　※「001」で「varchar(3)」だと３桁で「0001」で「varchar(4)」だと４桁

２．UNION ALLの下のクエリのゼロを増やして、VARCHARの桁数も合わせる。
　　「CAST(FORMAT(1 + idx, ‘000‘) AS VARCHAR(3))」
　　※「000」で「varchar(3)」だと３桁で「0000」で「varchar(4)」だと４桁

注意点

WHERE句の「＜」を「＜＝」にしなければならないと思いがちだが、それだと値が一つ増えるので「＜」にしておくこと。
また、
[SQL Server]ビューの作り方でも書いたように、FORMAT関数の戻り値はそのデータ型の最大桁数を返すので、「UNION ALL」の上下で桁数を合わせること。

[スポンサードリンク]

IT☆ぱらだいす

全てのITエンジニアの役に立ちたい