Oracle SQL SELECT7 : WITH句を使ったSELECT文
- SELECT文の重複する処理のWITH句による共通化
- WITH句を複数宣言する方法
- WITH句を再帰的に使った連番生成の方法
- WITH句を再帰的に使った連続日付の生成方法
- 連続日付に関連してDATE型について
おすすめ書籍
- 図解入門よくわかる最新Oracleデータベースの基本と仕組み[第4版] (How‐nual Visual Guide Book)
- Oracleの基本 ~データベース入門から設計/運用の初歩まで
- [改訂第4版]SQLポケットリファレンス
SELECT文の重複する処理のWITH句による共通化
SELECT文の中で何度も同じ副問合せを記述することがありますが、 その都度Oracleでの解析等が行われるので速度的にも問題ですし、 SQL文としても記述を簡単にしたいものです。その方法として、副問合せの部分を WITH句 で宣言し、その後の SELECT文 の中で利用します。 言葉の説明ではよく分からないので、実際の例を示します。
SQL> WITH TEST_WITH AS (
2 SELECT *
3 FROM TT_売上
4 WHERE 担当者コード IN (1, 2, 4)
5 )
6 SELECT *
7 FROM TEST_WITH
8 WHERE 得意先コード = 1
9 UNION
10 SELECT *
11 FROM TEST_WITH
12 WHERE 得意先コード = 2
13 UNION
14 SELECT *
15 FROM TEST_WITH
16 WHERE 得意先コード = 3;
売上番号 売上日 得意先コード 担当者コード
---------- -------- ------------ ------------
1 02-11-01 1 1
2 02-11-03 2 4
4 02-11-13 3 2
この例では「TT_売上」の「担当者コード」が「1, 2, 4」のデータを全てSELECTする部分を WITH句 で宣言しています。
そのデータを基にそれ以降の SELECT文 の中で「得意先コード」がそれぞれ「1」「2」「3」と個別のSELECT結果を UNION で連結しています。
処理内容は特に難しいことは行っていませんが WITH句 を使えば各種利用ができると思います。
WITH句を複数宣言する方法
WITH句 を複数宣言する場合には先頭のSELECT文には WITH を記述しますが、 そのあとのSELECT文はカンマで区切って WITH句 の名前を直後に記述します。
SQL> WITH TEST_WITH1 AS (
2 SELECT *
3 FROM TT_売上
4 WHERE 担当者コード IN (1, 2)
5 )
6 , TEST_WITH2 AS (
7 SELECT *
8 FROM TT_売上
9 WHERE 担当者コード IN (7, 9)
10 )
11 SELECT *
12 FROM TEST_WITH1
13 UNION
14 SELECT *
15 FROM TEST_WITH2;
売上番号 売上日 得意先コード 担当者コード
---------- -------- ------------ ------------
1 02-11-01 1 1
4 02-11-13 3 2
5 02-11-15 5 7
6 02-11-15 1 9
この例では「TT_売上」の「担当者コード」が「1, 2」のデータ及び、「TT_売上」の「担当者コード」が「7, 9」のデータを全てSELECTする WITH句 を別々に宣言しています。
（それぞれ「TEST_WITH1」「TEST_WITH2」の名前で宣言）
それぞれのデータを別々にSELECTしUNIONで連結しています。
WITH句を再帰的に使った連番生成の方法
WITH句 の宣言の中で自分自身を呼出すことで再帰的な処理が行えます。これを利用して連番を返す例を示します。
SQL> WITH SEQLIST(SEQNO) AS(
2 SELECT 1
3 FROM DUAL
4 UNION ALL
5 SELECT SEQNO + 1
6 FROM SEQLIST
7 WHERE SEQNO < 10
8 )
9 SELECT * FROM SEQLIST;
SEQNO
----------
1
2
3
4
5
6
7
8
9
10
10行が選択されました。
WITH句を再帰的に使った連続日付の生成方法
連番生成の方法に倣って、今度は日付の連続リストの生成を行います。連番生成の SEQNO が日付型データの SEQDATE にして日付型データを順次生成する様にします。 （以下のSQLはシステム日付から10日間の日付のリストを作成しています。）
SQL> WITH DAYLIST(SEQDATE) AS ( 2 SELECT SYSDATE 3 FROM DUAL 4 UNION ALL 5 SELECT SEQDATE + 1 6 FROM DAYLIST 7 WHERE SEQDATE < (SYSDATE + (10 - 1)) 8 ) 9 SELECT * FROM DAYLIST; SEQDATE -------- 19-10-07 19-10-08 19-10-09 19-10-10 19-10-11 19-10-12 19-10-13 19-10-14 19-10-15 19-10-16 10行が選択されました。
但しこの方法は Oracle 12c から可能な様で Oracle 11g で実行させると以下の様にエラーが出てしまいます。
SQL> WITH DAYLIST(SEQDATE) AS (
2 SELECT SYSDATE
3 FROM DUAL
4 UNION ALL
5 SELECT SEQDATE + 1
6 FROM DAYLIST
7 WHERE SEQDATE < (SYSDATE + (10 - 1))
8 )
9 SELECT * FROM DAYLIST;
SELECT SEQDATE + 1
*
行5でエラーが発生しました。:
ORA-01790: 式には対応する式と同じデータ型を持つ必要があります
そこで連番生成の最後のSELECT文を少し変えて日付を出力する様にしました。
SQL> WITH SEQLIST(SEQNO) AS( 2 SELECT 1 3 FROM DUAL 4 UNION ALL 5 SELECT SEQNO + 1 6 FROM SEQLIST 7 WHERE SEQNO < 10 8 ) 9 SELECT SYSDATE + (SEQNO - 1) FROM SEQLIST; SYSDATE+ -------- 19-10-07 19-10-08 19-10-09 19-10-10 19-10-11 19-10-12 19-10-13 19-10-14 19-10-15 19-10-16 10行が選択されました。
連続日付に関連してDATE型について
上にも記述しましたが連続日付を生成する為に日付に整数値を加算することで、その日からの加算された後の日付を生成できます。 これは以下のページにも記していますが DATE型 に加減算を行うことで対象日付の N日後 または N日前 を取得できます。⇒Oracle SQL その他・Tips・DATE型の考察
この例として以下のSQL文でシステム日付に加減算を行い、「当日」「翌日」「明後日」「前日」「一昨日」の日付を取得しています。
SQL> SELECT 2 SYSDATE + 0 AS "当日" 3 ,SYSDATE + 1 AS "翌日" 4 ,SYSDATE + 2 AS "明後日" 5 ,SYSDATE - 1 AS "前日" 6 ,SYSDATE - 2 AS "一昨日" 7 FROM DUAL; 当日 翌日 明後日 前日 一昨日 -------- -------- -------- -------- -------- 19-10-07 19-10-08 19-10-09 19-10-06 19-10-05
