7 Replies Latest reply: Jan 31, 2013 7:14 PM by 972990 RSS

    SELECT文の書き方

    972990
      create table test (      no varchar2(10),
                     pk1 varchar2(10),
                     pk2 varchar2(10),
                     pk3 date);
      ALTER TABLE test ADD CONSTRAINT test_PK PRIMARY KEY (pk1,pk2,pk3) ;

      insert into test values('1','pk1','pk2','2010/01/01');
      insert into test values('2','pk1','pk2','2010/02/01');
      insert into test values('3','pk1','pk2','2010/03/01');
      insert into test values('3','pk1','pk2','2010/04/01');
      insert into test values('4','pk1','pk2','2010/05/01');
      insert into test values('4','pk10','pk20','2010/04/01');


      上記のようなテーブルがあり、そのテーブルを検索できる画面があります。
      画面上では検索条件をユーザに入力させ、検索結果を表示します。
      検索項目はほぼ全部の列で、自由に指定できます。

      検索結果として表示させる内容は検索キーに基く最新の日付のデータを主データとし、pk3を除く同一主キーのデータの中で日付が大きいものを2件目のデータとして表示させたいのです。

      例えばユーザがno=3で検索した場合、検索キーに基く最新の日付のデータが主データとなります。

      insert into test values('3','pk1','pk2','2010/03/01');
      insert into test values('3','pk1','pk2','2010/04/01'); → 主データ

      2件目のデータはpk3を除く同一主キーのデータの中で日付が最大のものなので以下のデータを表示させたいです。
      その際、主データはのぞきます。

      insert into test values('1','pk1','pk2','2010/01/01');
      insert into test values('2','pk1','pk2','2010/02/01');
      insert into test values('3','pk1','pk2','2010/03/01');
      insert into test values('4','pk1','pk2','2010/05/01'); →2件目のデータ

      例えばユーザがpk1=pk1と検索した場合、検索キーに基く最新の日付のデータが主データとなります。
      insert into test values('1','pk1','pk2','2010/01/01');
      insert into test values('2','pk1','pk2','2010/02/01');
      insert into test values('3','pk1','pk2','2010/03/01');
      insert into test values('3','pk1','pk2','2010/04/01');
      insert into test values('4','pk1','pk2','2010/05/01'); →主データ

      2件目のデータはpk3を除く同一主キーのデータの中で日付が最大のものなので以下のデータを表示させたいです。
      その際、主データはのぞきます。
      insert into test values('1','pk1','pk2','2010/01/01');
      insert into test values('2','pk1','pk2','2010/02/01');
      insert into test values('3','pk1','pk2','2010/03/01');
      insert into test values('3','pk1','pk2','2010/04/01');→2件目のデータ

      2件目のデータを主データをのぞいて検索しなければならない点と順位付けがうまくいかず、困っています。
      そして、主データを1、2件目のデータを2というように順位をつけて画面上でソートして表示したいのですが、何かよい方法はありますでしょうか?

      非常にわかりづらいかもしれませんが、よろしくお願いいたします。
        • 1. Re: SELECT文の書き方
          tmtsmic
          こんにちは。

          無理矢理書いてみました。
          もっと簡単にならないんかな・・・orz
          WITH
          TBL0 AS
          (SELECT NO, PK1, PK2, PK3 FROM TEST),
          TBL1 AS
          (SELECT DENSE_RANK() OVER 
                  (PARTITION BY PK1,PK2
                  ORDER BY PK1,PK2,PK3 DESC) AS RNK,
                  NO, PK1, PK2, PK3
           FROM TEST
           WHERE NO = '3')
          -- WHERE PK1 = 'pk1')    ※指定条件に合わせて切替
          -- WHERE PK2 = 'pk2')
          SELECT '1' AS "順位", TBL0.NO, TBL0.PK1, TBL0.PK2, TBL0.PK3
          FROM TBL0,TBL1
          WHERE TBL1.RNK = 1
            AND TBL0.PK1 = TBL1.PK1
            AND TBL0.PK2 = TBL1.PK2
            AND TBL0.PK3 = TBL1.PK3
          UNION ALL
          SELECT '2' AS "順位", TBL0.NO, TBL0.PK1, TBL0.PK2, TBL0.PK3
          FROM TBL0
          WHERE (TBL0.PK1, TBL0.PK2, TBL0.PK3) IN
            (SELECT PK1, PK2, MAX(PK3) PK3_MAX
             FROM TBL0
             WHERE 
              EXISTS
              (SELECT 'X' FROM TBL1
               WHERE TBL0.PK1 = TBL1.PK1
                 AND TBL0.PK2 = TBL1.PK2)
              AND NOT EXISTS
              (SELECT 'X' FROM TBL1
               WHERE TBL1.RNK = 1
                 AND TBL0.PK1 = TBL1.PK1
                 AND TBL0.PK2 = TBL1.PK2
                 AND TBL0.PK3 = TBL1.PK3)
             GROUP BY PK1, PK2
            )
          ORDER BY 3,4,1
          
          ◎「NO = '3’」の場合
          順位 NO  PK1 PK2 PK3
          ---------------------------------
            1 3  pk1 pk2 2010/04/01
            2 4  pk1 pk2 2010/05/01
          
          ◎「PK1 = 'pk1'」の場合
          順位 NO  PK1 PK2 PK3
          ---------------------------------
            1 4  pk1 pk2 2010/05/01
            2 3  pk1 pk2 2010/04/01
          現段階ではおそらく抜けが山ほどあるでしょうが
          とりあえず、叩き台のSQLとして。
          PK3を条件指定することは今のところ考えていません。

          ちなみに、以下のようなデータは考慮しなくてよいのでしょうか?
          3とかpk1で絞り込んだ時、期待する出方がちょっとわからないです。
          insert into test values('3','pk1','pkx','2010/04/01');
          insert into test values('3','pk1','pkx','2010/05/01');


          ※OSとかOracleのバージョンは書いた方がいいですよ。
          • 2. Re: SELECT文の書き方
            972990
            OSはLinux 64-bit for AMD、DBのバージョンは10.2.0.3.0です。

            SQLですが別作業のためまだ見れずにいます。
            明日、拝見させていただき、ご返信いたしますので宜しくお願いいたします。
            お返事ありがとうございました。
            • 3. Re: SELECT文の書き方
              972990
              昨日教えていただいた方法でデータを取得することができました!
              exists、not existsの使用方法などとても参考になりました。

              本当にありがとうございました。
              • 4. Re: SELECT文の書き方
                Aketi Jyuuzou
                式のリストの比較を駆使してみました :-)
                with test(no,pk1,pk2,pk3) as(
                select '1','pk1' ,'pk2' ,'2010/01/01' from dual union all
                select '2','pk1' ,'pk2' ,'2010/02/01' from dual union all
                select '3','pk1' ,'pk2' ,'2010/03/01' from dual union all
                select '3','pk1' ,'pk2' ,'2010/04/01' from dual union all
                select '4','pk1' ,'pk2' ,'2010/05/01' from dual union all
                select '4','pk10','pk20','2010/04/01' from dual),
                MainData as(
                select 1 as seq,no,pk1,pk2,pk3
                from (select no,pk1,pk2,pk3,
                      Row_Number() over(order by pk3 desc) as rn
                        from test
                       where NO = '3'
                       /*where PK1 = 'pk1'*/)
                 where rn = 1),
                SubData as(
                select 2 as seq,no,pk1,pk2,pk3
                from (select no,pk1,pk2,pk3,
                      Row_Number() over(order by pk3 desc) as rn
                        from test
                       where (pk1,pk2,pk3) != (select pk1,pk2,pk3 from MainData)
                         and (pk1,pk2) = (select pk1,pk2 from MainData))
                 where rn = 1)
                select seq,no,pk1,pk2,pk3 from MainData
                union all
                select seq,no,pk1,pk2,pk3 from SubData
                order by seq;
                
                SEQ  NO   PK1   PK2   PK3
                ---  ---  ----  ----  ----------
                  1  3    pk1   pk2   2010/04/01
                  2  4    pk1   pk2   2010/05/01
                • 5. Re: SELECT文の書き方
                  972990
                  実はその後問題が発覚。
                  -------------
                  ちなみに、以下のようなデータは考慮しなくてよいのでしょうか?
                  3とかpk1で絞り込んだ時、期待する出方がちょっとわからないです。
                  insert into test values('3','pk1','pkx','2010/04/01');
                  insert into test values('3','pk1','pkx','2010/05/01');
                  -------------
                  no=3と条件を指定した場合、以下のデータを表示させたいです。

                  insert into test values('1','pk1','pk2','2010/01/01');
                  insert into test values('2','pk1','pk2','2010/02/01');
                  insert into test values('3','pk1','pk2','2010/03/01'); →主データ1
                  insert into test values('3','pk1','pk2','2010/04/01');→主データ2
                  insert into test values('4','pk1','pk2','2010/05/01');→主データ1に対する過去データとして表示、主データ2に対する過去データとして表示
                  insert into test values('4','pk10','pk20','2010/04/01');

                  期待する結果としてはこのように出力させたいです。

                  順序1,'3','pk1','pk2','2010/03/01' →主データ1
                  順序2,'4','pk1','pk2','2010/05/01'
                  順序1,'3','pk1','pk2','2010/04/01'→主データ2
                  順序2,'4','pk1','pk2','2010/05/01'

                  WITH
                  TBL0 AS
                  (SELECT NO, PK1, PK2, PK3 FROM TEST),
                  TBL1 AS
                  (SELECT DENSE_RANK() OVER
                  (PARTITION BY PK1,PK2
                  ORDER BY PK1,PK2,PK3 DESC) AS RNK,
                  NO, PK1, PK2, PK3
                  FROM TEST
                  WHERE NO = '3')
                  -- WHERE PK1 = 'pk1') ※指定条件に合わせて切替
                  -- WHERE PK2 = 'pk2')
                  SELECT '1' AS "順位", TBL0.NO, TBL0.PK1, TBL0.PK2, TBL0.PK3
                  FROM TBL0,TBL1
                  WHERE TBL0.PK1 = TBL1.PK1
                  AND TBL0.PK2 = TBL1.PK2
                  AND TBL0.PK3 = TBL1.PK3
                  UNION ALL
                  SELECT '2' AS "順位", TBL0.NO, TBL0.PK1, TBL0.PK2, TBL0.PK3
                  FROM TBL0
                  WHERE (TBL0.PK1, TBL0.PK2, TBL0.PK3) IN
                  (SELECT PK1, PK2, MAX(PK3) PK3_MAX
                  FROM TBL0
                  WHERE
                  EXISTS
                  (SELECT 'X' FROM TBL1
                  WHERE TBL0.PK1 = TBL1.PK1
                  AND TBL0.PK2 = TBL1.PK2)
                  AND NOT EXISTS
                  (SELECT 'X' FROM TBL1
                  WHERE TBL0.PK1 = TBL1.PK1
                  AND TBL0.PK2 = TBL1.PK2
                  AND TBL0.PK3 = TBL1.PK3)
                  GROUP BY PK1, PK2
                  )
                  ORDER BY 3,4,1

                  が今のところ一番近いSQLなのですが、「2」の結果を重複させて2件表示させたいです。

                       順位     NO     PK1     PK2     PK3
                       1     3     pk1     pk2     2010/03/01
                       1     3     pk1     pk2     2010/04/01
                       2     4     pk1     pk2     2010/05/01

                  私の説明の仕方が悪かったです。本当に申し訳ございません。
                  aketij さんの方法も参考にさせていただき、現在奮闘中です。

                  Edited by: 969987 on 2013/02/01 8:57
                  • 6. Re: SELECT文の書き方
                    tmtsmic
                    >OSはLinux 64-bit for AMD、DBのバージョンは10.2.0.3.0です。
                    上記のバージョンでは明智さんのSQLはエラーになりますね。
                    11.2.0.1なら大丈夫でした。
                    # 新しい機能なのかな?


                    <10g、WindowsServer>
                    Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
                    ・・・
                    with test(no,pk1,pk2,pk3) as(
                    *
                    行1でエラーが発生しました。:
                    ORA-32033: 列はサポートされていません。


                    <11g、WindowsServer>
                    Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
                    ・・・
                      SEQ  NO  PK1   PK2  PK3
                    --------------------------------------------------------
                        1   3  pk1   pk2  2010/04/01
                        2   4  pk1   pk2  2010/05/01
                    • 7. Re: SELECT文の書き方
                      972990
                      その後、ユーザさんから連絡があり、主データは1件のみということになったので、今までご教授いただいた方法で実装できそうです。
                      aketijさんの方法もこれから実装して、パフォーマンスのテストなどを行ってみたいと思います。