帳票データなどで登録されているデータ数に左右されず、
固定行数でデータを取得したい場合があります。
SQLで存在しない行を取得する方法
キー内で複数行のデータを管理しているデータから固定の行数分
情報を作り出して取得するSQLです。
全キー５行のデータとして取得する
まずは、各キー内で異なる件数のデータを作成します。
CREATE TABLE test_tbl
(
キー VARCHAR(3)
,インデックス INT
,項目１ VARCHAR(20)
,項目２ 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 キー,インデックス
実行結果は
キー インデックス 項目１ 項目２ 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
,項目１ VARCHAR(20)
,項目２ 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.項目１ ,'' ) AS 項目１
,ISNULL( test_tbl.項目２, '' ) AS 項目２
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]
キー インデックス 項目１ 項目２
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]
になります。
これで各キーが５行ずつになって取得できました。
どうしても固定行数で処理したい時に役立つと思います。
「WHERE seq_tbl.インデックス < 5」の「5」の数字を
変更することで固定行の行数を変更できます。
