いつもお世話になっています。
以下の処理を実現するUPDATE文を教えて頂きたく、質問にあげさせて頂きました。
※現在、処理を「実績登録用UPDATE」と「予定登録用UPDATE」のそれぞれ作成すれば
いけそうなのですが、1つのUPDATE文では実現出来ないでしょうか?
・・・実績登録用は作成したので、最後に付けています
以上、よろしくお願いします。
【処理】
トランより店マスタの更新を行う。
・トランの区分が”1"の場合は、店マスタの実績欄へトランの日付情報をセットする。
※店マスタの実績日は過去3日分を累積している為、 実績2を実績3へ、実績1を実績2へ
移してから、トランの日付を実績1欄にセットする
※区分”1”のレコードは、店コード毎に1件しか存在しない
・トランの区分が”2"の場合は、店マスタの予定欄へトランの日付情報をセットする。
※店マスタの予定日は、当処理が実行される時点では既にクリアされており、毎回トランに存在する
直近3日分を予定欄へセットする
※区分”2”のレコードは、店コード毎に複数件存在する)
例)
トラン(キー:店CD・日付)
店CD 日 付 区分
┼───┼───┼──┼
│ 0001 │01/10 │ 1 │→マスタの実績1に登録されます
│ 0001 │01/15 │ 2 │
│ 0002 │01/20 │ 2 │→マスタの予定1に登録されます
│ 0002 │01/30 │ 2 │→マスタの予定2に登録されます
│ 0003 │01/10 │ 1 │→マスタの実績1に登録されます
│ 0005 │01/10 │ 1 │→マスタの実績1に登録されます
│ 0005 │01/20 │ 2 │→マスタの予定1に登録されます
│ 0005 │01/30 │ 2 │→マスタの予定2に登録されます
│ 0005 │02/10 │ 2 │→マスタの予定3に登録されます
│ 0005 │02/20 │ 2 │→マスタにはMAX3件までしか登録出来ないのでスキップ
│ 0005 │03/01 │ 2 │→マスタにはMAX3件までしか登録出来ないのでスキップ
マスタ(キー:店CD)
店CD│実績1 実績2 実績3│予定1 予定2 予定3│
┼───┼───┼───┼───┼───┼───┼───┼
│ 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 │ │ │ │
【希望する結果】
店CD│実績1 実績2 実績3│予定1 予定2 予定3│
┼───┼───┼───┼───┼───┼───┼───┼
│ 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
UPDATE マスタ SET (
実績1,実績2,実績3
)=( SELECT
日付,MAX(TRN.実績1),MAX(TRN.実績2),MAX(TRN.実績3)
FROM マスタ LEFT JOIN トラン TRN ON 店CD = 店CD
WHERE 区分 = 1
GROUP BY トラン.店CD, トラン日付)
WHERE EXISTS(
SELECT 'TRUE'
FROM マスタ
WHERE マスタ.店CD = トラン.店CD
AND マスタ.日付 = トラン.日付
AND トラン.店CD = 1
);
No.1
- 回答日時:
ちょっと長くなってしまいましたが、これでどうでしょうか?
oracle10g XE では希望結果になりました。
ただ、ご提示のデータだと店CD:0003の予定1はnullのままのはずなので、そこが相違しますが。
--全角でインデントしてます
update マスタ
set (実績1,実績2,実績3,予定1,予定2,予定3) = (
select
nvl(TRN.実績,マスタ.実績1)
,case when TRN.実績 is not null then マスタ.実績1 else マスタ.実績2 end
,case when TRN.実績 is not null then マスタ.実績2 else マスタ.実績3 end
,TRN.予定1,TRN.予定2,TRN.予定3
from (
select
店CD
,max(case when 区分 = '1' then 日付 else null end) 実績
,max(case when 区分 = '2' and recnum = 1 then 日付 else null end) 予定1
,max(case when 区分 = '2' and recnum = 2 then 日付 else null end) 予定2
,max(case when 区分 = '2' and recnum = 3 then 日付 else null end) 予定3
from (
select 店CD,日付,区分,row_number() over(partition by 店CD, 区分 order by 日付) recnum
from トラン)
where recnum <= 3
group by 店CD) TRN
where マスタ.店CD = TRN.店CD)
where exists (select * from トラン where マスタ.店CD = トラン.店CD);
記載された更新SQLでは値の数が合っていませんし、トランには実績Nという項目は存在しないので
エラーで実行することすら出来ないと思うのですが・・?
yamada_g 様
いつもありがとうございます。
頂いたUPDATE文をお借りして、テストしてみます。
私の頭では、まだ理解出来ていないので、またお力をお借りするかも
しれませんが、その時はよろしくお願いします。
※私の載せたUPDATE文、おかしかったです。
項目をあわせて、INNERJOINしたら、一応!?動いてたので・・
見直しが足りませんでした。
No.2ベストアンサー
- 回答日時:
No.1です。
先ほどのSQLについて少し説明しますね。1.一番内側のselect文(トランを直接参照していることろ)で、
「直近3日分を予定欄へセットする」を満たすために、店CD・区分ごとに日付順でならべた行番号を振ります。
このとき、実績データ(区分='1')には1件しかない前提なので必ず1が振られます。
2.その一つ外のselect文では、1.で振った行番号が3以下という条件で抽出します。
これにより、実績データと予定データの直近3件が取れます。
ここで、区分と1.で振った行番号を使って実績と予定1~3を取得します。
3.そして一番外側のselect文で、マスタと結合して各項目に設定する値を取得しています。
「実績2を実績3へ、実績1を実績2へ移してから、トランの日付を実績1欄にセットする」とあるので、
実績1は、2.で実績が取得できていればその値を設定し、取得できていなければ現在の値を再設定(=更新しない)。
実績2・3は、2.で実績が取得できていればそれぞれ実績1・2を設定し、取得できていなければ現在の値を再設定(=更新しない)。
予定に関しては、2.で取得した予定1~3をそのまま設定。
という感じです。
内側からひとつずつselect文を実行していくと、やっていることが分かると思います。
もちろん、もっといいやり方もあると思いますが参考にしてみてください。
yamada_g 様
親切丁寧なご説明、ありがとうございます。
先ほどのSQL、完璧に動作しました。
本当にUPDATE文一つで動くとは思っていなかったので、
驚いています。
現在PGに入り、今後も分からないことだらけで質問を
あげることになると思います。
また、よろしくお願いします。
本当にありがとうございました。
お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!
似たような質問が見つかりました
- Excel(エクセル) エクセル関数の詳しい方に質問です 2 2023/01/12 12:48
- SQL Server AccessのInsertクエリのあとつづけてDeleteクエリを行いたいがSQLでどう書いたらいい 3 2023/05/27 14:12
- Excel(エクセル) vba userformで漢字を全角カタカナに 2 2022/07/24 15:38
- その他(バイク) 原付バイク。住民票のない市役所で名義変更は可能か? 1 2022/06/25 12:12
- Visual Basic(VBA) access count数を変数に格納 2 2022/03/30 19:21
- Android(アンドロイド) Xperia 10 III LiteでeSIMは複数同時に登録できますか? 4 2023/07/01 23:37
- MySQL 下記の問合せを行うクエリを、PhpMyAdminで作成して実行せよ。 第二回模試の3科目の各得点と合 1 2023/04/25 18:02
- 薬剤師・登録販売者・MR 登録販売者のダブルワーク 現在、登録販売者(店舗管理者)として働いていますが、近々転職しようと思って 1 2023/04/27 15:36
- Excel(エクセル) VBA でvlookup エラーなどは削除したい 8 2022/12/30 04:03
- その他(クラウドサービス・オンラインストレージ) Microsoft Listと同じ使い方が出来るサービス 1 2022/11/21 09:01
関連するカテゴリからQ&Aを探す
おすすめ情報
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
Accessで期間指定の売上合計と...
-
項目名の制限について
-
各店舗毎の最大売上の項目を取...
-
SQLでNOT INと!=ALLの違い
-
SQL文(県名一覧・・)
-
列名無効について
-
フォームで検索 エラーの表示...
-
SQLでグループ化した結果の件数...
-
Excel 2019 のピボットテーブル...
-
【Access】フォームで自動計算...
-
アクセスクエリの計算
-
エクセルVBAで5行目からオート...
-
列が存在しないと言われる
-
Accessでテーブル名やクエリ名...
-
「直需」の意味を教えてください
-
Oracle 2つのDate型の値の差を...
-
Accessのフィールド数が255しか...
-
INSERTできるレコード数を制限...
-
MS ACCESS 商品価格変更時の該...
-
Oracleのビュー作成時に「指定...
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
SQLでグループ化した結果の件数...
-
ORA-01722: 数値が無効です
-
項目名の制限について
-
MySQLで改行を含む文の登録のし...
-
フォームで検索 エラーの表示...
-
オラクルSQLの累計値取得方法に...
-
列名無効について
-
【 困 】 ストアドプロシージャ
-
Accessで期間指定の売上合計と...
-
Accessで複数テーブルのJoin
-
アクセスのクエリについて(前...
-
SQL文 2つのテーブルから、グ...
-
外部結合のSQLの書き方について
-
♪Oracle SQL 処理が成功した時...
-
SQLを教えてください
-
特殊なレコードの取得方法
-
SQLでNOT INと!=ALLの違い
-
各店舗毎の最大売上の項目を取...
-
SQL抽出方法に悩んでいます④
-
改行コードを削除して取得する...
おすすめ情報