SQLで連番になってるレコードが欲しい時ありませんか?
例えばSEQや日付がランダムに格納されているテーブルがあって、
INNER JOINをすると歯抜けになってしまう様な場合に
ガイドとして空レコードをくっつけたいなど。
Oracleならテーブル等を事前に用意しなくても内部ビューでいけます。
数値の場合
SQL
WITH
PARAMS AS(
SELECT
3 AS START_NUM,
7 AS END_NUM
FROM
DUAL
)
SELECT
START_NUM + (ROWNUM - 1) AS SEQ
FROM
DUAL,
PARAMS
CONNECT BY
LEVEL <= END_NUM - START_NUM + 1
;
実行結果
SEQ |
---|
3 |
4 |
5 |
6 |
7 |
START_NUM
に開始値、END_NUM
に終了値を設定すると、
その範囲の連番をレコード(行に分けて)取得できます。
日付の場合
SQL
WITH
PARAMS AS(
SELECT
'20180625' AS SYMD,
'20180701' AS EYMD
FROM
DUAL
)
SELECT
TO_DATE(SYMD, 'yyyymmdd') + (ROWNUM - 1) AS DAYS
FROM
DUAL,
PARAMS
CONNECT BY
LEVEL <= TO_DATE(EYMD, 'yyyymmdd') - TO_DATE(SYMD, 'yyyymmdd') + 1
;
実行結果
DAYS |
---|
2018/6/25 |
2018/6/26 |
2018/6/27 |
2018/6/28 |
2018/6/29 |
2018/6/30 |
2018/7/1 |
SYMD
に開始日付、EYMD
に終了日付を設定すると、
その範囲の連日をレコード(行に分けて)取得できます。
使い道(例)
カレンダー画面を作成するに当たって、土日の他に祝日と休業日をマークしたい。
祝日と休業日はイベントマスタに事前に登録しておく。
開始日付に月初、終了日付に月末を指定、イベントマスタをLEFT JOINして上記SQLを実行する。
ユーザプログラムはただ帰ってきたレコードをそのまま表示するだけでカレンダーを実装できます。ラクチン♪
応用として「今月の営業日数は何日?」もカウント取るだけでできちゃいます。ラクチン♪