SQLで存在しない行を生成し固定行数で処理したい時に役立つ方法

プログラミング SQL Server

帳票データなどで登録されているデータ数に左右されず、
固定行数でデータを取得したい場合があります。

スポンサーリンク

SQLで存在しない行を取得する方法

キー内で複数行のデータを管理しているデータから固定の行数分
情報を作り出して取得するSQLです。

全キー5行のデータとして取得する

まずは、各キー内で異なる件数のデータを作成します。

CREATE TABLE test_tbl
(
     キー           VARCHAR(3)
    ,インデックス  INT
    ,項目1        VARCHAR(20)
    ,項目2        VARCHAR(20)
)
INSERT INTO test_tbl VALUES ( '001','1','001-1','ABC')
INSERT INTO test_tbl VALUES ( '001','2','001-2','DEF')
INSERT INTO test_tbl VALUES ( '002','1','002-1','GHI')
INSERT INTO test_tbl VALUES ( '002','2','002-2','JKL')
INSERT INTO test_tbl VALUES ( '002','3','002-3','MNO')
INSERT INTO test_tbl VALUES ( '003','1','003-1','PQR')
SELECT * FROM test_tbl ORDER BY キー,インデックス

実行結果は

キー    インデックス    項目1    項目2
001                1    001-1     ABC
001                2    001-2     DEF
002                1    002-1     GHI
002                2    002-2     JKL
002                3    002-3     MNO
003                1    003-1     PQR

になります。

これを固定にして存在しない行のデータを作り出して取得します。

CREATE TABLE test_tbl
(
キー VARCHAR(3)
,インデックス INT
,項目1 VARCHAR(20)
,項目2 VARCHAR(20)
)
INSERT INTO test_tbl VALUES ( ‘001’,’1′,’001-1′,’ABC’)
INSERT INTO test_tbl VALUES ( ‘001’,’2′,’001-2′,’DEF’)
INSERT INTO test_tbl VALUES ( ‘002’,’1′,’002-1′,’GHI’)
INSERT INTO test_tbl VALUES ( ‘002’,’2′,’002-2′,’JKL’)
INSERT INTO test_tbl VALUES ( ‘002’,’3′,’002-3′,’MNO’)
INSERT INTO test_tbl VALUES ( ‘003’,’1′,’003-1′,’PQR’)
;
WITH seq_tbl AS
(
SELECT
1 AS インデックス
UNION ALL
SELECT
seq_tbl.インデックス+ 1
FROM
seq_tbl
WHERE
seq_tbl.インデックス < 5 ) SELECT A.キー AS キー ,A.インデックス AS インデックス ,ISNULL( test_tbl.項目1 ,'' ) AS 項目1 ,ISNULL( test_tbl.項目2, '' ) AS 項目2 FROM ( SELECT test_tbl.キー AS キー ,seq_tbl.インデックス AS インデックス FROM seq_tbl ,test_tbl GROUP BY test_tbl.キー ,seq_tbl.インデックス ) AS A LEFT OUTER JOIN test_tbl ON A.キー = test_tbl.キー AND A.インデックス = test_tbl.インデックス ORDER BY A.キー ,A.インデックス DROP TABLE test_tbl [/sql] 実行結果は [text] キー インデックス 項目1 項目2 001 1 001-1 ABC 001 2 001-2 DEF 001 3 001 4 001 5 002 1 002-1 GHI 002 2 002-2 JKL 002 3 002-3 MNO 002 4 002 5 003 1 003-1 PQR 003 2 003 3 003 4 003 5 [/text] になります。 これで各キーが5行ずつになって取得できました。 どうしても固定行数で処理したい時に役立つと思います。 「WHERE seq_tbl.インデックス < 5」の「5」の数字を 変更することで固定行の行数を変更できます。

タイトルとURLをコピーしました