質問をすることでしか得られない、回答やアドバイスがある。

15分調べてもわからないことは、質問しよう!

ただいまの
回答率

89.06%

Oracleの階層問い合わせで計算値を子に引き継ぎたい

解決済

回答 2

投稿 編集

  • 評価
  • クリップ 0
  • VIEW 546

nemui_00

score 5

Oracleの階層問い合わせで、親の数量と親に対する数量を元に子の数量を順次計算していくことは可能でしょうか?
以下のようなテーブルで最上位の数量が変更された場合、子の数量を再計算したいです。

  • 更新前
ID 親ID 親に対する必要数 数量
ID1 2
ID2 ID1 2 4
ID3 ID2 3 12
ID4 ID2 1 4
  • 更新後(ID1の数量を2→1に更新)
ID 親ID 親に対する必要数 数量
ID1 1
ID2 ID1 2 4
ID3 ID2 3 12
ID4 ID2 1 4
  • 更新後テーブルから求めたい結果
    親の数量*親に対する必要数で子の数量を再計算
ID 親ID 親に対する必要数 数量(求めたい値) 備考
ID1 1 最上位
ID2 ID1 2 2 1(親数量)*2(親に対する必要数)
ID3 ID2 3 6 2(親数量)*3(親に対する必要数)
ID4 ID2 1 2 2(親数量)*1(親に対する必要数)

どなたかよい方法があればご教示お願い致します。
以下のSQLがやりたいことに近いのですが、最上位の子までしか計算できません。

SELECT DISTINCT
  LEVEL
  , ID
  , ID_PARENT
  , RT_PARENT 親に対する必要数
  , VL_ITEM 数量
  , (PRIOR VL_ITEM * RT_PARENT) 計算後数量 
FROM
  TEST 
START WITH
  ID = 'ID1' 
CONNECT BY
  PRIOR ID = ID_PARENT;

-- CREATE
create table TEST (
  ID VARCHAR2(5)
  , ID_PARENT VARCHAR2(5)
  , VL_ITEM NUMBER(4,0)
  , RT_PARENT NUMBER(4,0)
  , constraint TEST_PKC primary key (ID)
) ;

comment on column TEST.ID is 'ID';
comment on column TEST.ID_PARENT is '親ID';
comment on column TEST.VL_ITEM is '数量';
comment on column TEST.RT_PARENT is '親に対する必要数';

-- INSERT
insert into TEST(ID,ID_PARENT,VL_ITEM,RT_PARENT) values ('ID1',null,1,null);
insert into TEST(ID,ID_PARENT,VL_ITEM,RT_PARENT) values ('ID2','ID1',4,2);
insert into TEST(ID,ID_PARENT,VL_ITEM,RT_PARENT) values ('ID3','ID2',12,3);
insert into TEST(ID,ID_PARENT,VL_ITEM,RT_PARENT) values ('ID4','ID2',4,1);
  • 気になる質問をクリップする

    クリップした質問は、後からいつでもマイページで確認できます。

    またクリップした質問に回答があった際、通知やメールを受け取ることができます。

    クリップを取り消します

  • 良い質問の評価を上げる

    以下のような質問は評価を上げましょう

    • 質問内容が明確
    • 自分も答えを知りたい
    • 質問者以外のユーザにも役立つ

    評価が高い質問は、TOPページの「注目」タブのフィードに表示されやすくなります。

    質問の評価を上げたことを取り消します

  • 評価を下げられる数の上限に達しました

    評価を下げることができません

    • 1日5回まで評価を下げられます
    • 1日に1ユーザに対して2回まで評価を下げられます

    質問の評価を下げる

    teratailでは下記のような質問を「具体的に困っていることがない質問」、「サイトポリシーに違反する質問」と定義し、推奨していません。

    • プログラミングに関係のない質問
    • やってほしいことだけを記載した丸投げの質問
    • 問題・課題が含まれていない質問
    • 意図的に内容が抹消された質問
    • 過去に投稿した質問と同じ内容の質問
    • 広告と受け取られるような投稿

    評価が下がると、TOPページの「アクティブ」「注目」タブのフィードに表示されにくくなります。

    質問の評価を下げたことを取り消します

    この機能は開放されていません

    評価を下げる条件を満たしてません

    評価を下げる理由を選択してください

    詳細な説明はこちら

    上記に当てはまらず、質問内容が明確になっていない質問には「情報の追加・修正依頼」機能からコメントをしてください。

    質問の評価を下げる機能の利用条件

    この機能を利用するためには、以下の事項を行う必要があります。

質問への追記・修正、ベストアンサー選択の依頼

  • nemui_00

    2019/12/19 16:56

    ご指摘・アドバイスありがとうございます!
    指摘いただいた内容で質問を修正しました。

    キャンセル

  • sazi

    2019/12/19 19:27

    希望する結果を追記して下さい。

    キャンセル

  • nemui_00

    2019/12/20 08:53

    ご指摘ありがとうございます。希望する結果を追記しました。

    キャンセル

回答 2

checkベストアンサー

0

階層を辿る必要があるので、階層問合せではできません。
再帰問合せを用います。
津島博士のパフォーマンス講座 第31回 再帰的問合せについて

手打ちで検証もしていませんが、イメージは以下の様な感じだと思います

with tree(ID, ID_PARENT, VL_ITEM, RT_PARENT, lvl, clc_VL_ITEM) as (
  select ID, ID_PARENT, VL_ITEM, RT_PARENT
       , 1, VL_ITEM
  from  TEST
  where ID='ID1'
union all
  select t.ID, t.ID_PARENT, t.VL_ITEM, t.RT_PARENT
       , tr.lvl+1, tr.clc_VL_ITEM * t.VL_ITEM
  from  TEST t inner join tree tr
        on  t.ID_PARENT=tr.ID
)
select * from tree

投稿

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

  • 2019/12/20 13:39

    回答いただいたSQLを参考にしたところ、うまくいきました。
    大変勉強になりました。ありがとうございます。

    キャンセル

0

見ていてわかりにくいのでSQLをちょっと編集しました。

計算後数量 ってわからないので憶測で、質問に希望されるSELECT結果もあると良いですが、インラインビューを使えば良いってことでしょうか?

テーブル名、列名や別名に(今回の質問のように)1文字でもマルチバイト文字を使いたいときはダブルクォートで囲んでください。

SQL> SELECT DISTINCT
  2      LEVEL
  3    , ID
  4    , ID_PARENT
  5    , RT_PARENT AS "親に対する必要数"
  6    , VL_ITEM AS "数量"
  7    , PRIOR CALC_ITEM AS "計算後数量"
  8  FROM(
  9      SELECT
 10          ID
 11        , ID_PARENT
 12        , RT_PARENT
 13        , VL_ITEM
 14        , VL_ITEM * RT_PARENT AS CALC_ITEM
 15      FROM TEST
 16      )
 17  START WITH ID = 'ID1'
 18  CONNECT BY PRIOR ID = ID_PARENT
 19  ORDER BY
 20      LEVEL
 21    , ID
 22    , ID_PARENT;

     LEVEL ID         ID_PARENT  親に対する必要数       数量 計算後数量
---------- ---------- ---------- ---------------- ---------- ----------
         1 ID1                                             2
         2 ID2        ID1                       2          4
         3 ID3        ID2                       3         12          8
         3 ID4        ID2                       1          4          8

SQL>

投稿

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

  • 2019/12/20 09:02

    ご回答・アドバイスありがとうございます。説明不足で重ね重ね申し訳ありません。
    更新後テーブルから求めたい結果を追加しましたので、ご確認いただけますと幸いです。

    キャンセル

15分調べてもわからないことは、teratailで質問しよう!

  • ただいまの回答率 89.06%
  • 質問をまとめることで、思考を整理して素早く解決
  • テンプレート機能で、簡単に質問をまとめられる

同じタグがついた質問を見る