ＵＰＤＡＴＥ文を教えて頂けないでしょうか

質問

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

以下の処理を実現するＵＰＤＡＴＥ文を教えて頂きたく、質問にあげさせて頂きました。
※現在、処理を「実績登録用ＵＰＤＡＴＥ」と「予定登録用ＵＰＤＡＴＥ」のそれぞれ作成すれば
　いけそうなのですが、１つのＵＰＤＡＴＥ文では実現出来ないでしょうか？
　・・・実績登録用は作成したので、最後に付けています
以上、よろしくお願いします。

【処理】
トランより店マスタの更新を行う。
・トランの区分が”１"の場合は、店マスタの実績欄へトランの日付情報をセットする。
　※店マスタの実績日は過去３日分を累積している為、　実績２を実績３へ、実績１を実績２へ
　　移してから、トランの日付を実績１欄にセットする
　※区分”１”のレコードは、店コード毎に１件しか存在しない

・トランの区分が”２"の場合は、店マスタの予定欄へトランの日付情報をセットする。
　※店マスタの予定日は、当処理が実行される時点では既にクリアされており、毎回トランに存在する
　　直近３日分を予定欄へセットする
　※区分”２”のレコードは、店コード毎に複数件存在する）

例）
トラン（キー：店ＣＤ・日付）
　店ＣＤ　日　付　区分　
┼───┼───┼──┼
│ 0001 │01/10 │ 1 │→マスタの実績１に登録されます
│ 0001 │01/15 │ 2 │

│ 0002 │01/20 │ 2 │→マスタの予定１に登録されます
│ 0002 │01/30 │ 2 │→マスタの予定２に登録されます

│ 0003 │01/10 │ 1 │→マスタの実績１に登録されます

│ 0005 │01/10 │ 1 │→マスタの実績１に登録されます
│ 0005 │01/20 │ 2 │→マスタの予定１に登録されます
│ 0005 │01/30 │ 2 │→マスタの予定２に登録されます
│ 0005 │02/10 │ 2 │→マスタの予定３に登録されます
│ 0005 │02/20 │ 2 │→マスタにはMAX３件までしか登録出来ないのでスキップ
│ 0005 │03/01 │ 2 │→マスタにはMAX３件までしか登録出来ないのでスキップ

マスタ（キー：店ＣＤ）
　店ＣＤ│実績１　実績２　実績３│予定１　予定２　予定３│
┼───┼───┼───┼───┼───┼───┼───┼
│ 0001 │01/05 │12/30 │12/25 │ │ │ │
│ 0002 │12/30 │12/15 │11/30 │ │ │ │
│ 0003 │01/01 │12/20 │12/10 │ │ │ │
│ 0004 │01/07 │01/06 │01/05 │　　　│ │ │
│ 0005 │01/01 │12/20 │12/10 │ │ │ │

【希望する結果】
　店ＣＤ│実績１　実績２　実績３│予定１　予定２　予定３│
┼───┼───┼───┼───┼───┼───┼───┼
│ 0001 │01/10 │01/05 │12/30 │01/15 │ │ │
│ 0002 │12/30 │12/15 │11/30 │01/20 │01/30 │ │
│ 0003 │01/10 │01/01 │12/20 │01/07 │ │ │
│ 0004 │01/07 │01/06 │01/05 │　　　│ │ │
│ 0005 │01/10 │01/01 │12/20 │01/20 │01/30 │02/10 │

■実績更新用ＵＰＤＡＴＥ
UPDATE マスタ SET (
実績１,実績２,実績３
)=( SELECT
日付,MAX(TRN.実績１),MAX(TRN.実績２),MAX(TRN.実績３)
FROM マスタ LEFT JOIN トラン TRN ON 店ＣＤ = 店ＣＤ
WHERE 区分 = 1
GROUP BY トラン.店ＣＤ, トラン日付)
WHERE EXISTS(
SELECT 'TRUE'
FROM マスタ
WHERE マスタ.店ＣＤ = トラン.店ＣＤ
AND マスタ.日付　 = トラン.日付
AND トラン.店ＣＤ = 1
);

回答No.2

yamada_g · ベストアンサー

No.1です。先ほどのSQLについて少し説明しますね。

1.一番内側のselect文（トランを直接参照していることろ）で、
　「直近３日分を予定欄へセットする」を満たすために、店ＣＤ・区分ごとに日付順でならべた行番号を振ります。
　このとき、実績データ（区分='1'）には1件しかない前提なので必ず1が振られます。

2.その一つ外のselect文では、1.で振った行番号が3以下という条件で抽出します。
　これにより、実績データと予定データの直近3件が取れます。
　ここで、区分と1.で振った行番号を使って実績と予定１～３を取得します。

3.そして一番外側のselect文で、マスタと結合して各項目に設定する値を取得しています。
　「実績２を実績３へ、実績１を実績２へ移してから、トランの日付を実績１欄にセットする」とあるので、
　実績１は、2.で実績が取得できていればその値を設定し、取得できていなければ現在の値を再設定(=更新しない）。
　実績２・３は、2.で実績が取得できていればそれぞれ実績１・２を設定し、取得できていなければ現在の値を再設定(=更新しない）。
　予定に関しては、2.で取得した予定１～３をそのまま設定。

という感じです。
内側からひとつずつselect文を実行していくと、やっていることが分かると思います。
もちろん、もっといいやり方もあると思いますが参考にしてみてください。

お礼

yamada_g 様

親切丁寧なご説明、ありがとうございます。

先ほどのＳＱＬ、完璧に動作しました。
本当にＵＰＤＡＴＥ文一つで動くとは思っていなかったので、
驚いています。

現在ＰＧに入り、今後も分からないことだらけで質問を
あげることになると思います。
また、よろしくお願いします。

本当にありがとうございました。

回答No.1

yamada_g

ちょっと長くなってしまいましたが、これでどうでしょうか？
oracle10g XE では希望結果になりました。
ただ、ご提示のデータだと店CD:0003の予定１はnullのままのはずなので、そこが相違しますが。

--全角でインデントしてます
update マスタ
set (実績１,実績２,実績３,予定１,予定２,予定３) = (
　select
　　nvl(TRN.実績,マスタ.実績１)
　　,case when TRN.実績 is not null then マスタ.実績１ else マスタ.実績２ end
　　,case when TRN.実績 is not null then マスタ.実績２ else マスタ.実績３ end
　　,TRN.予定１,TRN.予定２,TRN.予定３
　from (
　　select
　　　店ＣＤ
　　　,max(case when 区分 = '1' then 日付 else null end) 実績
　　　,max(case when 区分 = '2' and recnum = 1 then 日付 else null end) 予定１
　　　,max(case when 区分 = '2' and recnum = 2 then 日付 else null end) 予定２
　　　,max(case when 区分 = '2' and recnum = 3 then 日付 else null end) 予定３
　　from (
　　　select 店ＣＤ,日付,区分,row_number() over(partition by 店ＣＤ, 区分 order by 日付) recnum
　　　from トラン)
　　where recnum <= 3
　　group by 店ＣＤ) TRN
　where マスタ.店ＣＤ = TRN.店ＣＤ)
where exists (select * from トラン where マスタ.店ＣＤ = トラン.店ＣＤ);

記載された更新SQLでは値の数が合っていませんし、トランには実績Nという項目は存在しないので
エラーで実行することすら出来ないと思うのですが・・？

お礼

yamada_g 様

いつもありがとうございます。

頂いたＵＰＤＡＴＥ文をお借りして、テストしてみます。
私の頭では、まだ理解出来ていないので、またお力をお借りするかも
しれませんが、その時はよろしくお願いします。

※私の載せたＵＰＤＡＴＥ文、おかしかったです。
項目をあわせて、INNERJOINしたら、一応!?動いてたので・・
見直しが足りませんでした。