12 返信 最新の回答 日時: Apr 24, 2012 1:18 AM ユーザー:hamadeguchi

    CONNECT BY に関して

    930339
      教えて下さい。

      あるデータにて部品の構成図のようなものを作成しようと考えております。

      そこで、CONNECT BY を応用しながら作成しようと考えていますが、その際に、親品番を
      指定して下位層を表示させる事は容易にできるかと思われますが、指定した品番に対し、
      上位層(トップ親品番、及び直親品番)を検索するような事は可能でしょうか???

      現在、想定しているテーブルレイアウトとしては、単純に

      品番 品名 親品番 ・・・

      としてありますが、これだけで上記の条件を実現する事は可能でしょうか???

      以下テストテーブルデータ

      品番 品名 親品番

      A AAA
      B BBB A
      C CCC A
      B1 BBB-1 B
      C1 CCC-1 H
      C1 CCC-1 C
      H HHH

      抽出結果として、C1を指定した場合、トップ親品番として、

       A、H

      が抽出され、直親品番としては、

       C、H

      が抽出されるような形としたいと考えています。

      テーブルの項目を増やしてSQLでなんとかするのか?どのようなやり方で上記のような
      事が実現可能か?を教えて頂ければと思います。

      よろしくお願いします。
        • 1. Re: CONNECT BY に関して
          hamadeguchi
          直親は通常の品番検索ですよね

          トップはSYS_CONNECT_BY_PATHなどで出来そうですが
          http://docs.oracle.com/cd/E16338_01/server.112/b56299/functions183.htm

          品番がキーではないのでしょうか?
          C1が2件ありますが。。。

          試してませんがこういう状態だとCONNECTがうまく動作しなかったような
          • 2. Re: CONNECT BY に関して
            930339
            hamadeguchi様、ご回答ありがとうございます。

            直親のケースは、私も難しく考え過ぎていました。

            単純検索でできますね!

            ありがとうございます!

            SYS_CONNECT_BY_PATH、の件ですが、確かにトップを指定して下位の構成を表示させた
            場合、それぞれのパスは表示されますが、子供の品番を指定して、トップ品番を検索するには
            どのようなSQLを記述すれば良いでしょうか、、、

            start with には、C1 を指定したいです。


            尚、C1が2件あるケースですが、CONNECT BY では、問題なく動くような、、、

            現状ではなんとか動いています、、、

            教えて下さい。よろしくお願いします。
            • 3. Re: CONNECT BY に関して
              hamadeguchi
              あ、そうですか。昔のバージョンでなんかの条件で無限ループになったりしたことがあったので。。。

              トップはCONNECT_BY_ROOTでした。
              http://docs.oracle.com/cd/E16338_01/server.112/b56299/queries003.htm#i2069380
              http://codezine.jp/article/detail/2696?p=2
              • 4. Re: CONNECT BY に関して
                930339
                hamadeguchi様、ご回答ありがとうございます。

                CONNECT_BY_ROOTを使用するのですよね、、、私もそれかと思い、一生懸命調べていますが、
                どうも私の力では解決できません。

                CONNECT_BY_ROOTでは、START WITHにトップ品番を記載した場合は、問題なく、トップ品番を
                返してくれますが、末端の子部品をSTART WITHにした場合は、その上にあるトップ品番を拾ってくれません。

                当然と言えば当然ですが、、、

                子部品からトップ品番を検索する事ができるSQLの記述の仕方を教えて頂ければ幸いです。

                私の知識不足で申し訳ありません。

                よろしくお願いします。
                • 5. Re: CONNECT BY に関して
                  hamadeguchi
                  こういう場合はサブクエリを使います。

                  SELECT * FROM (
                  START WITHにトップ品番を記載したCONNECT BYのSELECT文にROOTと品番を含める
                  )
                  WHERE 品番=xxx
                  • 6. Re: CONNECT BY に関して
                    930339
                    hamadeguchi様、ご回答ありがとうございます!

                    申し訳ありません、SQLがそこまで得意でない為、せっかく教えて頂いたSQLを理解する事が、、、

                    実際に私の作成したテーブル対し、作成して頂ければ本当に助かります。

                    お手間をお掛けして大変申し訳ありませんが、よろしくお願いします。
                    • 7. Re: CONNECT BY に関して
                      tmtsmic
                      こんにちは。

                      >実際に私の作成したテーブル対し、作成して頂ければ本当に助かります。
                      そうしてもらうためには
                      まず、テーブルのCREATE文とINSERT文くらいは提示しましょうね。
                      あと、もう少しデータ件数(&パターン)があるといいです。

                      見ず知らずの人に頼む際には
                      相手がある程度手間を省くことができるよう
                      自ら努力することも必要です。
                      • 8. Re: CONNECT BY に関して
                        930339
                        tmtsmic様、ご指摘ありがとうございます。

                        そうですね、準備しないといけないですね!

                        以下に簡単ですが、テーブル作成とデータインサートのSQLを作成しました。

                        これでなんとかお願いしたいと思います。

                        よろしくお願いします。

                        【SQL】

                         create table T_TEST1
                        (
                          品番 char(3) ,
                          品名 char(3) ,
                          親品番 char(3)
                        )


                        insert into T_TEST (品番,品名,親品番) values('A','AAA',null);
                        insert into T_TEST (品番,品名,親品番) values('B','BBB','A');
                        insert into T_TEST (品番,品名,親品番) values('B1','BBB-1','B');
                        insert into T_TEST (品番,品名,親品番) values('C1','CCC-1','C');
                        insert into T_TEST (品番,品名,親品番) values('C','CCC','A');
                        insert into T_TEST (品番,品名,親品番) values('C1','CCC-1','H');
                        insert into T_TEST (品番,品名,親品番) values('H','HHH',null);
                        insert into T_TEST (品番,品名,親品番) values('B2','BBB-2','B1');
                        insert into T_TEST (品番,品名,親品番) values('B3','BBB-3','B1');
                        insert into T_TEST (品番,品名,親品番) values('C2','CCC-2','C');
                        insert into T_TEST (品番,品名,親品番) values('D','DDD','A');
                        insert into T_TEST (品番,品名,親品番) values('B1','BBB-1','H');
                        • 9. Re: CONNECT BY に関して
                          hamadeguchi
                          まだ見てらっしゃるかな?
                          環境がないので間違ってるかもしれませんが、
                          以下のようにすると取得できると思います。
                          FROMのテーブルの代わりにSELECT文を括弧でくくってかくと
                          テーブルと同様に使えます。これをサブクエリや副問い合わせと呼びます。
                          SELECT CONNECT_BY_ROOT 品番 トップ品番
                                ,品番
                          START WITH 親品番 IS NULL
                          CONNECT BY 品番 = 親品番
                          ORDER BY 1,2
                          でトップ品番が取得できるとして
                          SELECT 品番,トップ品番
                            FROM (
                          SELECT CONNECT_BY_ROOT 品番 トップ品番
                                ,品番
                          START WITH 親品番 IS NULL
                          CONNECT BY 品番 = 親品番
                          )
                          WHERE 品番 = xxxx
                          ORDER BY 品番,トップ品番
                          という感じで実現可能かと思います
                          • 10. Re: CONNECT BY に関して
                            tmtsmic
                            こんにちは。

                            hamadeguchiさんのを見て、動くように修正してみました。
                            結果はどうでしょうか?

                            実は今日がCONNECT BYを初めて使用する日だったりするので
                            解釈が間違っていたらほんとすいません。m(_ _ )m
                            入念にパターン検査をお願いします。
                            SELECT  品番,  直親品番,  トップ親品番
                            FROM
                              (
                                SELECT  品番,
                                        親品番 AS 直親品番,
                                        CONNECT_BY_ROOT 品番 AS トップ親品番
                                FROM    T_TEST
                                START WITH  親品番 IS NULL
                                CONNECT BY PRIOR  品番 = 親品番
                              )
                            WHERE    品番 = '[指定する品番]'
                            ORDER BY  品番,  トップ親品番,  直親品番
                            
                            
                            ●[指定する品番]=C1の場合
                            
                            品番 直親品番 トップ親品番
                            ----------------
                             C1    C      A  
                             C1    H      H  
                            
                            ●[指定する品番]=B3の場合
                            
                            品番 直親品番 トップ親品番
                            ----------------
                             B3    B1      A  
                             B3    B1      H  
                            
                            ●[指定する品番]=C2の場合
                            
                            品番 直親品番 トップ親品番
                            ----------------
                             C2    C      A  
                            【環境】
                             Windows Server 2008 R2
                             Oracle11g Enterprise Edition 11.2.0.1.0(64bit)

                            【参考】
                             サブクエリの中身
                            品番  直親品番  トップ親品番
                            ----------------
                             A            A
                             B     A       A
                             B1    B       A
                             B1    H       H
                             B2    B1      A
                             B2    B1      H
                             B3    B1      H
                             B3    B1      A
                             C     A       A
                             C1    C       A
                             C1    H       H
                             C2    C       A
                             D     A       A
                             H            H
                            # >SQLがそこまで得意でない為
                            # 私もそんなことを言っていた時代がありました。
                            # SQLが面白いと感じた時が成長のチャンスです。
                            • 11. Re: CONNECT BY に関して
                              930339
                              tmtsmic様、hamadeguchi様、本当にありがとうございました。

                              私も朝からこのSQLと格闘をしておりました。

                              まさしく私のイメージどおりの結果です。

                              良く良くSQLを深く考えると、結構プログラムでやっていた事さえもSQLできてしまうものですね。

                              何回もお付き合い頂き、本当に感謝しています。

                              ありがとうございました!
                              • 12. Re: CONNECT BY に関して
                                hamadeguchi
                                あっ、FROM句書き忘れてました!
                                tmtsmicさんフォローありがとう!
                                質問者さんおめでとう!

                                FYI:
                                SQLで出来ることについては以下も参考になりますよ
                                http://www.geocities.jp/oraclesqlpuzzle/

                                Edited by: hamadeguchi on Apr 23, 2012 6:16 PM