4 Replies Latest reply: Feb 21, 2013 5:48 PM by 972990 RSS

    集計データの表示

    972990
      いつもお世話になっております。

      月別の売上データを持つ中間集計表があります。
      その中間集計表から条件に合うデータを検索して集計表示させたいのですが、
      非常に長いSQLになってしまい、困っています。
      SQLの記述を短縮させる方法を教えてください。(ビューなどを使用するのも可です)

      ORACLEのバージョンはOracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bitです。

      □中間集計表
      CREATE TABLE SALES_DATA_TEST
      (COMPANY_CODE VARCHAR2(2),
      DEPT_CODE VARCHAR2(2),
      SHUBETU_FLG VARCHAR2(1),
      M04 NUMBER(5,3),
      M05 NUMBER(5,3),
      M06 NUMBER(5,3),
      M07 NUMBER(5,3),
      M08 NUMBER(5,3),
      M09 NUMBER(5,3),
      M10 NUMBER(5,3),
      M11 NUMBER(5,3),
      M12 NUMBER(5,3),
      M01 NUMBER(5,3),
      M02 NUMBER(5,3),
      M03 NUMBER(5,3));

      □データ
      INSERT INTO SALES_DATA_TEST VALUES('01','01','1','10.05','10.05','10.05','10.05','10.05','10.05','10.05','10.05','10.05','10.05','10.05','10.05');
      INSERT INTO SALES_DATA_TEST VALUES('01','01','1','10.05','10.05','10.05','10.05','10.05','10.05','10.05','','10.05','10.05','10.05','');
      INSERT INTO SALES_DATA_TEST VALUES('01','01','2','10.05','10.05','10.05','10.05','10.05','10.05','10.05','10.05','10.05','10.05','10.05','10.05');
      INSERT INTO SALES_DATA_TEST VALUES('01','02','1','10.05','','10.05','10.05','10.05','10.05','10.05','10.05','10.05','10.05','10.05','10.05');
      INSERT INTO SALES_DATA_TEST VALUES('01','02','2','10.05','10.05','10.05','10.05','10.05','10.05','10.05','10.05','10.05','10.05','10.05','10.05');
      INSERT INTO SALES_DATA_TEST VALUES('01','02','1','10.05','10.05','10.05','10.05','10.05','10.05','10.05','10.05','10.05','10.05','10.05','10.05');
      INSERT INTO SALES_DATA_TEST VALUES('01','03','2','10.05','10.05','10.05','10.05','10.05','10.05','10.05','10.05','10.05','10.05','10.05','10.05');
      INSERT INTO SALES_DATA_TEST VALUES('01','03','2','10.05','10.05','10.05','10.05','10.05','10.05','10.05','10.05','10.05','10.05','10.05','10.05');
      INSERT INTO SALES_DATA_TEST VALUES('01','03','1','10.05','','10.05','10.05','10.05','10.05','10.05','10.05','10.05','10.05','10.05','10.05');
      INSERT INTO SALES_DATA_TEST VALUES('02','01','2','10.05','10.05','10.05','10.05','10.05','10.05','10.05','10.05','10.05','10.05','10.05','10.05');
      INSERT INTO SALES_DATA_TEST VALUES('02','01','1','10.05','10.05','10.05','10.05','10.05','10.05','10.05','10.05','10.05','10.05','10.05','10.05');
      INSERT INTO SALES_DATA_TEST VALUES('02','01','2','10.05','10.05','10.05','10.05','10.05','10.05','','10.05','10.05','10.05','10.05','10.05');
      INSERT INTO SALES_DATA_TEST VALUES('02','02','1','10.05','10.05','10.05','10.05','10.05','10.05','10.05','10.05','10.05','10.05','10.05','10.05');
      INSERT INTO SALES_DATA_TEST VALUES('02','02','1','10.05','10.05','10.05','10.05','10.05','10.05','10.05','10.05','10.05','10.05','10.05','10.05');
      INSERT INTO SALES_DATA_TEST VALUES('02','02','1','10.05','10.05','10.05','10.05','10.05','10.05','10.05','10.05','10.05','10.05','10.05','10.05');
      INSERT INTO SALES_DATA_TEST VALUES('02','03','2','10.05','10.05','10.05','10.05','10.05','10.05','10.05','10.05','10.05','10.05','10.05','10.05');
      INSERT INTO SALES_DATA_TEST VALUES('02','03','2','','10.05','10.05','10.05','','10.05','10.05','10.05','10.05','','10.05','10.05');
      INSERT INTO SALES_DATA_TEST VALUES('02','03','1','10.05','10.05','10.05','10.05','10.05','10.05','10.05','10.05','10.05','10.05','10.05','10.05');

      □SELECT文
      SELECT '売上データ(種別1)' TITLE_NAME,
      TITLE_FLG,
      CASE
      WHEN TITLE_FLG IN ('01','02') THEN
      TOTAL
      ELSE
      ROUND(TOTAL, 0)
      END TOTAL,
      CASE
      WHEN TITLE_FLG IN ('01','02') THEN
      S_1Q
      ELSE
      ROUND(S_1Q)
      END S_1Q,
      CASE
      WHEN TITLE_FLG IN ('01','02') THEN
      S_2Q
      ELSE
      ROUND(S_2Q)
      END S_2Q,
      CASE
      WHEN TITLE_FLG IN ('01','02') THEN
      S_3Q
      ELSE
      ROUND(S_3Q)
      END S_3Q,
      CASE
      WHEN TITLE_FLG IN ('01','02') THEN
      S_4Q
      ELSE
      ROUND(S_4Q)
      END S_4Q,
      CASE
      WHEN TITLE_FLG IN ('01','02') THEN
      M04
      ELSE
      ROUND(M04)
      END M04,
      CASE
      WHEN TITLE_FLG IN ('01','02') THEN
      M05
      ELSE
      ROUND(M05)
      END M05,
      CASE
      WHEN TITLE_FLG IN ('01','02') THEN
      M06
      ELSE
      ROUND(M06)
      END M06
      FROM (SELECT '01' TITLE_FLG, A.*
      FROM (SELECT NVL(SUM(M04), 0) + NVL(SUM(M05), 0) + NVL(SUM(M06), 0) +
      NVL(SUM(M07), 0) + NVL(SUM(M08), 0) + NVL(SUM(M09), 0) +
      NVL(SUM(M10), 0) + NVL(SUM(M11), 0) + NVL(SUM(M12), 0) +
      NVL(SUM(M01), 0) + NVL(SUM(M02), 0) + NVL(SUM(M03), 0) TOTAL,
      NVL(SUM(M04), 0) + NVL(SUM(M05), 0) + NVL(SUM(M06), 0) S_1Q,
      NVL(SUM(M07), 0) + NVL(SUM(M08), 0) + NVL(SUM(M09), 0) S_2Q,
      NVL(SUM(M10), 0) + NVL(SUM(M11), 0) + NVL(SUM(M12), 0) S_3Q,
      NVL(SUM(M01), 0) + NVL(SUM(M02), 0) + NVL(SUM(M03), 0) S_4Q,
      NVL(SUM(M04), 0) M04,
      NVL(SUM(M05), 0) M05,
      NVL(SUM(M06), 0) M06
      FROM SALES_DATA_TEST
      WHERE COMPANY_CODE = '01'
      AND DEPT_CODE <> '01'
      AND SHUBETU_FLG = '1') A)
      UNION ALL
      SELECT '売上データ(種別2)' TITLE_NAME,
      TITLE_FLG,
      CASE
      WHEN TITLE_FLG IN ('01','02') THEN
      TOTAL
      ELSE
      ROUND(TOTAL)
      END TOTAL,
      CASE
      WHEN TITLE_FLG IN ('01','02') THEN
      S_1Q
      ELSE
      ROUND(S_1Q)
      END S_1Q,
      CASE
      WHEN TITLE_FLG IN ('01','02') THEN
      S_2Q
      ELSE
      ROUND(S_2Q)
      END S_2Q,
      CASE
      WHEN TITLE_FLG IN ('01','02') THEN
      S_3Q
      ELSE
      ROUND(S_3Q)
      END S_3Q,
      CASE
      WHEN TITLE_FLG IN ('01','02') THEN
      S_4Q
      ELSE
      ROUND(S_4Q)
      END S_4Q,
      CASE
      WHEN TITLE_FLG IN ('01','02') THEN
      M04
      ELSE
      ROUND(M04)
      END M04,
      CASE
      WHEN TITLE_FLG IN ('01','02') THEN
      M05
      ELSE
      ROUND(M05)
      END M05,
      CASE
      WHEN TITLE_FLG IN ('01','02') THEN
      M06
      ELSE
      ROUND(M06)
      END M06
      FROM (SELECT '02' TITLE_FLG, A.*
      FROM (SELECT NVL(SUM(M04), 0) + NVL(SUM(M05), 0) + NVL(SUM(M06), 0) +
      NVL(SUM(M07), 0) + NVL(SUM(M08), 0) + NVL(SUM(M09), 0) +
      NVL(SUM(M10), 0) + NVL(SUM(M11), 0) + NVL(SUM(M12), 0) +
      NVL(SUM(M01), 0) + NVL(SUM(M02), 0) + NVL(SUM(M03), 0) TOTAL,
      NVL(SUM(M04), 0) + NVL(SUM(M05), 0) + NVL(SUM(M06), 0) S_1Q,
      NVL(SUM(M07), 0) + NVL(SUM(M08), 0) + NVL(SUM(M09), 0) S_2Q,
      NVL(SUM(M10), 0) + NVL(SUM(M11), 0) + NVL(SUM(M12), 0) S_3Q,
      NVL(SUM(M01), 0) + NVL(SUM(M02), 0) + NVL(SUM(M03), 0) S_4Q,
      NVL(SUM(M04), 0) M04,
      NVL(SUM(M05), 0) M05,
      NVL(SUM(M06), 0) M06
      FROM SALES_DATA_TEST
      WHERE COMPANY_CODE = '02'
      AND DEPT_CODE <> '01'
      AND SHUBETU_FLG = '2') A)
      UNION ALL
      SELECT '総合計' TITLE_NAME,
      TITLE_FLG,
      CASE
      WHEN TITLE_FLG IN ('01','02') THEN
      TOTAL
      ELSE
      ROUND(TOTAL)
      END TOTAL,
      CASE
      WHEN TITLE_FLG IN ('01','02') THEN
      S_1Q
      ELSE
      ROUND(S_1Q)
      END S_1Q,
      CASE
      WHEN TITLE_FLG IN ('01','02') THEN
      S_2Q
      ELSE
      ROUND(S_2Q)
      END S_2Q,
      CASE
      WHEN TITLE_FLG IN ('01','02') THEN
      S_3Q
      ELSE
      ROUND(S_3Q)
      END S_3Q,
      CASE
      WHEN TITLE_FLG IN ('01','02') THEN
      S_4Q
      ELSE
      ROUND(S_4Q)
      END S_4Q,
      CASE
      WHEN TITLE_FLG IN ('01','02') THEN
      M04
      ELSE
      ROUND(M04)
      END M04,
      CASE
      WHEN TITLE_FLG IN ('01','02') THEN
      M05
      ELSE
      ROUND(M05)
      END M05,
      CASE
      WHEN TITLE_FLG IN ('01','02') THEN
      M06
      ELSE
      ROUND(M06)
      END M06
      FROM (SELECT '03' TITLE_FLG, A.*
      FROM (SELECT NVL(SUM(M04), 0) + NVL(SUM(M05), 0) + NVL(SUM(M06), 0) +
      NVL(SUM(M07), 0) + NVL(SUM(M08), 0) + NVL(SUM(M09), 0) +
      NVL(SUM(M10), 0) + NVL(SUM(M11), 0) + NVL(SUM(M12), 0) +
      NVL(SUM(M01), 0) + NVL(SUM(M02), 0) + NVL(SUM(M03), 0) TOTAL,
      NVL(SUM(M04), 0) + NVL(SUM(M05), 0) + NVL(SUM(M06), 0) S_1Q,
      NVL(SUM(M07), 0) + NVL(SUM(M08), 0) + NVL(SUM(M09), 0) S_2Q,
      NVL(SUM(M10), 0) + NVL(SUM(M11), 0) + NVL(SUM(M12), 0) S_3Q,
      NVL(SUM(M01), 0) + NVL(SUM(M02), 0) + NVL(SUM(M03), 0) S_4Q,
      NVL(SUM(M04), 0) M04,
      NVL(SUM(M05), 0) M05,
      NVL(SUM(M06), 0) M06
      FROM SALES_DATA_TEST) A);


      検索条件はCOMPANY_CODE、DEPT_CODEとなっており、アプリから設定。毎回変更します。
      その検索条件にしたがって、種別1、種別2、合計といった決まった結果を出力します。


      売上データ(種別1):SHUBETU_FLGが1のもの
      売上データ(種別2):SHUBETU_FLGが2のもの
      合計:全部の合計

      テーブルの構成などSQLは短縮して表示していますが、実際には種別が10以上存在するため、
      UNION ALLでいくつものSELECT文をつなげて、小計を出したり合計を出したりしています。

      単純なSQLのUNIONなのですが、3万行ぐらいになっており、
      なんとか短縮したいと考えております。
      そもそも、中間集計表の構成を変えた方がよいのかもしれないですが、皆様のお知恵を拝借できればと思っております。
      以上、宜しくお願いいたします。
        • 1. Re: 集計データの表示
          tmtsmic
          こんにちは。

          もう少しSQLを整頓して書いて頂いた方がいいですね。
          さすがに見づらいです。

          [CODE]
          SELECT * FROM DUAL
          [/CODE]

          実際は半角ですが、
          上記のように括るともう少し見やすくなります。
          SELECT * FROM DUAL
          Previewで確認しつつ、改行とかも工夫してみてください。


          ---

          ちなみに質問の方ですが
          似たSQLな様子ですから「表関数」なんかを
          使ってみるのもひとつの手かと。
          #私はあまり使ったことが無いですが・・・
          • 2. Re: 集計データの表示
            972990
            お返事ありがとうございます。
            codeの機能を知らなかったので、勉強になりました。
            SQLを修正しました。
            表関数について、もう少し調べてみます。
            ありがとうございます。
            SELECT '売上データ(種別1)' TITLE_NAME,
                   TITLE_FLG,
                   CASE
                       WHEN TITLE_FLG IN ('01', '02') THEN
                        TOTAL
                       ELSE
                        ROUND(TOTAL, 0)
                   END TOTAL,
                   CASE
                       WHEN TITLE_FLG IN ('01', '02') THEN
                        S_1Q
                       ELSE
                        ROUND(S_1Q)
                   END S_1Q,
                   CASE
                       WHEN TITLE_FLG IN ('01', '02') THEN
                        S_2Q
                       ELSE
                        ROUND(S_2Q)
                   END S_2Q,
                   CASE
                       WHEN TITLE_FLG IN ('01', '02') THEN
                        S_3Q
                       ELSE
                        ROUND(S_3Q)
                   END S_3Q,
                   CASE
                       WHEN TITLE_FLG IN ('01', '02') THEN
                        S_4Q
                       ELSE
                        ROUND(S_4Q)
                   END S_4Q,
                   CASE
                       WHEN TITLE_FLG IN ('01', '02') THEN
                        M04
                       ELSE
                        ROUND(M04)
                   END M04,
                   CASE
                       WHEN TITLE_FLG IN ('01', '02') THEN
                        M05
                       ELSE
                        ROUND(M05)
                   END M05,
                   CASE
                       WHEN TITLE_FLG IN ('01', '02') THEN
                        M06
                       ELSE
                        ROUND(M06)
                   END M06
              FROM (SELECT '01' TITLE_FLG, A.* 
                      FROM (SELECT NVL(SUM(M04), 0) + NVL(SUM(M05), 0) + NVL(SUM(M06), 0) +
                                   NVL(SUM(M07), 0) + NVL(SUM(M08), 0) + NVL(SUM(M09), 0) +
                                   NVL(SUM(M10), 0) + NVL(SUM(M11), 0) + NVL(SUM(M12), 0) +
                                   NVL(SUM(M01), 0) + NVL(SUM(M02), 0) + NVL(SUM(M03), 0) TOTAL,
                                   NVL(SUM(M04), 0) + NVL(SUM(M05), 0) + NVL(SUM(M06), 0) S_1Q,
                                   NVL(SUM(M07), 0) + NVL(SUM(M08), 0) + NVL(SUM(M09), 0) S_2Q,
                                   NVL(SUM(M10), 0) + NVL(SUM(M11), 0) + NVL(SUM(M12), 0) S_3Q,
                                   NVL(SUM(M01), 0) + NVL(SUM(M02), 0) + NVL(SUM(M03), 0) S_4Q,
                                   NVL(SUM(M04), 0) M04,
                                   NVL(SUM(M05), 0) M05,
                                   NVL(SUM(M06), 0) M06
                              FROM SALES_DATA_TEST
                             WHERE COMPANY_CODE = '01'
                               AND DEPT_CODE '01'
                               AND SHUBETU_FLG = '1') A)
            UNION ALL
            SELECT '売上データ(種別2)' TITLE_NAME,
                   TITLE_FLG,
                   CASE
                       WHEN TITLE_FLG IN ('01', '02') THEN
                        TOTAL
                       ELSE
                        ROUND(TOTAL)
                   END TOTAL,
                   CASE
                       WHEN TITLE_FLG IN ('01', '02') THEN
                        S_1Q
                       ELSE
                        ROUND(S_1Q)
                   END S_1Q,
                   CASE
                       WHEN TITLE_FLG IN ('01', '02') THEN
                        S_2Q
                       ELSE
                        ROUND(S_2Q)
                   END S_2Q,
                   CASE
                       WHEN TITLE_FLG IN ('01', '02') THEN
                        S_3Q
                       ELSE
                        ROUND(S_3Q)
                   END S_3Q,
                   CASE
                       WHEN TITLE_FLG IN ('01', '02') THEN
                        S_4Q
                       ELSE
                        ROUND(S_4Q)
                   END S_4Q,
                   CASE
                       WHEN TITLE_FLG IN ('01', '02') THEN
                        M04
                       ELSE
                        ROUND(M04)
                   END M04,
                   CASE
                       WHEN TITLE_FLG IN ('01', '02') THEN
                        M05
                       ELSE
                        ROUND(M05)
                   END M05,
                   CASE
                       WHEN TITLE_FLG IN ('01', '02') THEN
                        M06
                       ELSE
                        ROUND(M06)
                   END M06
              FROM (SELECT '02' TITLE_FLG, A.*
                      FROM (SELECT NVL(SUM(M04), 0) + NVL(SUM(M05), 0) + NVL(SUM(M06), 0) +
                                   NVL(SUM(M07), 0) + NVL(SUM(M08), 0) + NVL(SUM(M09), 0) +
                                   NVL(SUM(M10), 0) + NVL(SUM(M11), 0) + NVL(SUM(M12), 0) +
                                   NVL(SUM(M01), 0) + NVL(SUM(M02), 0) + NVL(SUM(M03), 0) TOTAL,
                                   NVL(SUM(M04), 0) + NVL(SUM(M05), 0) + NVL(SUM(M06), 0) S_1Q,
                                   NVL(SUM(M07), 0) + NVL(SUM(M08), 0) + NVL(SUM(M09), 0) S_2Q,
                                   NVL(SUM(M10), 0) + NVL(SUM(M11), 0) + NVL(SUM(M12), 0) S_3Q,
                                   NVL(SUM(M01), 0) + NVL(SUM(M02), 0) + NVL(SUM(M03), 0) S_4Q,
                                   NVL(SUM(M04), 0) M04,
                                   NVL(SUM(M05), 0) M05,
                                   NVL(SUM(M06), 0) M06
                              FROM SALES_DATA_TEST
                             WHERE COMPANY_CODE = '02'
                               AND DEPT_CODE '01'
                               AND SHUBETU_FLG = '2') A)
            UNION ALL
            SELECT '総合計' TITLE_NAME,
                   TITLE_FLG,
                   CASE
                       WHEN TITLE_FLG IN ('01', '02') THEN
                        TOTAL
                       ELSE
                        ROUND(TOTAL)
                   END TOTAL,
                   CASE
                       WHEN TITLE_FLG IN ('01', '02') THEN
                        S_1Q
                       ELSE
                        ROUND(S_1Q)
                   END S_1Q,
                   CASE
                       WHEN TITLE_FLG IN ('01', '02') THEN
                        S_2Q
                       ELSE
                        ROUND(S_2Q)
                   END S_2Q,
                   CASE
                       WHEN TITLE_FLG IN ('01', '02') THEN
                        S_3Q
                       ELSE
                        ROUND(S_3Q)
                   END S_3Q,
                   CASE
                       WHEN TITLE_FLG IN ('01', '02') THEN
                        S_4Q
                       ELSE
                        ROUND(S_4Q)
                   END S_4Q,
                   CASE
                       WHEN TITLE_FLG IN ('01', '02') THEN
                        M04
                       ELSE
                        ROUND(M04)
                   END M04,
                   CASE
                       WHEN TITLE_FLG IN ('01', '02') THEN
                        M05
                       ELSE
                        ROUND(M05)
                   END M05,
                   CASE
                       WHEN TITLE_FLG IN ('01', '02') THEN
                        M06
                       ELSE
                        ROUND(M06)
                   END M06
              FROM (SELECT '03' TITLE_FLG, A.*
                      FROM (SELECT NVL(SUM(M04), 0) + NVL(SUM(M05), 0) + NVL(SUM(M06), 0) +
                                   NVL(SUM(M07), 0) + NVL(SUM(M08), 0) + NVL(SUM(M09), 0) +
                                   NVL(SUM(M10), 0) + NVL(SUM(M11), 0) + NVL(SUM(M12), 0) +
                                   NVL(SUM(M01), 0) + NVL(SUM(M02), 0) + NVL(SUM(M03), 0) TOTAL,
                                   NVL(SUM(M04), 0) + NVL(SUM(M05), 0) + NVL(SUM(M06), 0) S_1Q,
                                   NVL(SUM(M07), 0) + NVL(SUM(M08), 0) + NVL(SUM(M09), 0) S_2Q,
                                   NVL(SUM(M10), 0) + NVL(SUM(M11), 0) + NVL(SUM(M12), 0) S_3Q,
                                   NVL(SUM(M01), 0) + NVL(SUM(M02), 0) + NVL(SUM(M03), 0) S_4Q,
                                   NVL(SUM(M04), 0) M04,
                                   NVL(SUM(M05), 0) M05,
                                   NVL(SUM(M06), 0) M06
                              FROM SALES_DATA_TEST) A);
            • 3. Re: 集計データの表示
              yamada
              インラインビューに同じようなSQLを何回も書くのならWITH句を使えばいいのでは。
              コピペしやすいように全ての箇所に入れているのかもしれませんが、
              必要のないCASE式は消してしまえばいいのではないでしょうか。
              WITH TMP_SUMMARY AS (
              SELECT  COMPANY_CODE, DEPT_CODE, SHUBETU_FLG, 
                      NVL(SUM(M04), 0) + NVL(SUM(M05), 0) + NVL(SUM(M06), 0) +
                      NVL(SUM(M07), 0) + NVL(SUM(M08), 0) + NVL(SUM(M09), 0) +
                      NVL(SUM(M10), 0) + NVL(SUM(M11), 0) + NVL(SUM(M12), 0) +
                      NVL(SUM(M01), 0) + NVL(SUM(M02), 0) + NVL(SUM(M03), 0) TOTAL,
                      NVL(SUM(M04), 0) + NVL(SUM(M05), 0) + NVL(SUM(M06), 0) S_1Q,
                      NVL(SUM(M07), 0) + NVL(SUM(M08), 0) + NVL(SUM(M09), 0) S_2Q,
                      NVL(SUM(M10), 0) + NVL(SUM(M11), 0) + NVL(SUM(M12), 0) S_3Q,
                      NVL(SUM(M01), 0) + NVL(SUM(M02), 0) + NVL(SUM(M03), 0) S_4Q,
                      NVL(SUM(M04), 0) M04,
                      NVL(SUM(M05), 0) M05,
                      NVL(SUM(M06), 0) M06
              FROM SALES_DATA_TEST
              GROUP BY COMPANY_CODE, DEPT_CODE, SHUBETU_FLG
              )
              SELECT  '売上データ(種別1)' TITLE_NAME,
                      '01' TITLE_FLG,
                      TOTAL,
                      S_1Q,
                      S_2Q,
                      S_3Q,
                      S_4Q,
                      M04,
                      M05,
                      M06
              FROM TMP_SUMMARY
              WHERE COMPANY_CODE = '01'
              AND DEPT_CODE = '01'
              AND SHUBETU_FLG = '1'
              UNION ALL 
              SELECT  '売上データ(種別2)' TITLE_NAME,
                      '02' TITLE_FLG,
                      TOTAL,
                      S_1Q,
                      S_2Q,
                      S_3Q,
                      S_4Q,
                      M04,
                      M05,
                      M06
              FROM TMP_SUMMARY
              WHERE COMPANY_CODE = '02'
              AND DEPT_CODE = '01'
              AND SHUBETU_FLG = '2'
              UNION ALL
              SELECT  '総合計' TITLE_NAME,
                      '03' TITLE_FLG,
                      ROUND(SUM(TOTAL)),
                      ROUND(SUM(S_1Q)),
                      ROUND(SUM(S_2Q)),
                      ROUND(SUM(S_3Q)),
                      ROUND(SUM(S_4Q)),
                      ROUND(SUM(M04)),
                      ROUND(SUM(M05)),
                      ROUND(SUM(M06))
              FROM TMP_SUMMARY
              提示のSQLと同様の結果になります。
              • 4. Re: 集計データの表示
                972990
                ご連絡が遅れてすみませんでした。
                お返事ありがとうございました。
                PL/SQLでは実装できないため、WITH句の使用してSQLを短くしたいと思います。ありがとうございました。
                前任者の作成したSQLで30000行あり困っていたので助かりました。
                これでなんとか半分に減らせそうです。