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

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

ただいまの
回答率

89.23%

Oracle SQLにおいて最終値+1の値から連番を振りたい

解決済

回答 2

投稿

  • 評価
  • クリップ 0
  • VIEW 2,003

hatsuk0i1216

score 14

SQLを軽く本で勉強しただけの初心者です。
簡単なSELECTとUPDATEならばできますが、WHILEやLOOPはなんとなとく意味がわかる程度で、コードを書いたことはありません。

【前提】
■keycode_masterという一つのテーブルを用意し、データが20万件程度入っている。

メーカー名 品番 管理用コード 採番用
AAA 10001 AAA001 1
AAA 10002 AAA002 2
AAA 10003 AAA003 3
AAA 10004 AAA004 4
BBB AB001 BBB001 1
BBB AB002 BBB002 2
BBB AB003 BBB003 3
CCC 10001 CCC001 1

■採番については下記のルールで実施
・採番用のフィールドにはメーカー毎の連番を入力。
・管理用キーコードは採番用のフィールド+メーカー名で生成。

下記のSQLでできた。

MERGE
INTO    "keycode_master" u
USING   (
        SELECT  rowid AS rid,
                ROW_NUMBER() OVER (PARTITION BY メーカー名 ORDER BY 品番) AS rn
        FROM    "keycode_master"
        )
ON      (u.rowid = rid)
WHEN MATCHED THEN
 UPDATE
 SET     採番用 = rn;
      管理用キーコード = メーカー名 || lpad(rn,10,'0');

【わからないところ】
■商品追加時は他のマスター(main_master)と品番でマージさせ、
メーカー名と品番をkeycode_masterにINSERTさせている。
★その際に自動で採番用の値+1からNULLの分だけ、自動採番をさせたい。

下図だと、メーカー名AAAには5と6を
メーカー名CCCには2を入れたい

メーカー名 品番 管理用コード 採番用
AAA 10001 AAA001 1
AAA 10002 AAA002 2
AAA 10003 AAA003 3
AAA 10004 AAA004 4
AAA 10005
AAA 10006
BBB AB001 BBB001 1
BBB AB002 BBB002 2
BBB AB003 BBB003 3
CCC 10001 CCC001 1
CCC 10002

【聞きたいこと】
どのようなロジックで組めばいいか、まったくわかりませんので、
考えられるパターンを教えてください。

組めるかはわかりませんが、考えられるロジックとしては、
メーカー名毎に最終の値と、NULLの値を取得して、
NULLの数だけ最終の値+1をしていくというループをしていく?
その場合は、CASEでメーカー名を指定する?

です。
よろしくお願いいたします。

  • 気になる質問をクリップする

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

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

    クリップを取り消します

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

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

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

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

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

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

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

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

    質問の評価を下げる

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

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

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

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

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

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

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

    詳細な説明はこちら

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

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

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

回答 2

checkベストアンサー

+1

SQLクエリで実施する方法を回答します。

質問文には書いていないですが、メーカー名と品番を主キーとしますね。
最終的な目標は以下のようになると考えます。

メーカー名 品番 管理用コード 採番
AAA 10005 5
AAA 10006 6
CCC 10002 2

まず、元の表から、更新前の採番値の最大値を列に連結するクエリを作ります。
max() over (partition by 条件)で作れます。

select
    maker_name
    ,hinban
    ,kanri_code
    ,saiban
    ,max(saiban) over (partition by  maker_name) max_saiban
from keycode_master tbl1
order by maker_name, hinban
;

MAK HINBA KANRI_     SAIBAN MAX_SAIBAN
--- ----- ------ ---------- ----------
AAA 10001 AAA001          1          4
AAA 10002 AAA002          2          4
AAA 10003 AAA003          3          4
AAA 10004 AAA004          4          4
AAA 10005                            4
AAA 10006                            4
BBB AB001 BBB001          1          3
BBB AB002 BBB002          2          3
BBB AB003 BBB003          3          3
CCC 10001 CCC001          1          1
CCC 10002                            1

11行が選択されました。 


MAX_SAIBAN に各メーカーごとの現時点での最大値が出てきました。

次に、この表を基に採番がNULLのレコードに対し、row_numberを最大値に加えたNEW_SAIBAN列を作成します。

with sub1 as (
select
    maker_name
    ,hinban
    ,kanri_code
    ,saiban
    ,max(saiban) over (partition by  maker_name) max_saiban
from keycode_master tbl1
order by maker_name, hinban
)
select
    maker_name
    ,hinban
    ,kanri_code
    ,saiban
    ,row_number() over (partition by maker_name order by maker_name, hinban) row_num_by_maker
    ,max_saiban + row_number() over (partition by maker_name order by maker_name, hinban) new_saiban
from sub1
    where sub1.saiban is null
    ;


MAK HINBA KANRI_     SAIBAN ROW_NUM_BY_MAKER NEW_SAIBAN
--- ----- ------ ---------- ---------------- ----------
AAA 10005                                  1          5
AAA 10006                                  2          6
CCC 10002                                  1          2


withで作った一時表のsub1は上のクエリと同一です。

最後に、この表とメーカー名、品番を連結してNEW_SAIBANを元表のSAIBANに更新するUPDATE文を作成します。

update keycode_master upd
set saiban = (
    with sub1 as (
        select
            maker_name
            ,hinban
            ,kanri_code
            ,saiban
            ,max(saiban) over (partition by  maker_name) max_saiban
        from keycode_master tbl1
        order by maker_name, hinban
        ),sub2 as (
            select
                maker_name
                ,hinban
                ,kanri_code
                ,saiban
                ,max_saiban + row_number() over (partition by maker_name order by maker_name, hinban) new_saiban
            from sub1
            where saiban is null
        )
        select
            new_saiban
        from sub2
        where upd.maker_name = sub2.maker_name
            and upd.hinban = sub2.hinban
            and sub2.saiban is null
    ) 
where saiban is null
;


最後に全体をSELECTしてみましょう。

select * from keycode_master;

MAK HINBA KANRI_     SAIBAN
--- ----- ------ ----------
AAA 10001 AAA001          1
AAA 10002 AAA002          2
AAA 10003 AAA003          3
AAA 10004 AAA004          4
AAA 10005                 5
AAA 10006                 6
BBB AB001 BBB001          1
BBB AB002 BBB002          2
BBB AB003 BBB003          3
CCC 10001 CCC001          1
CCC 10002                 2

11行が選択されました。 


以上のような手順で、作りたいデータを順番に生成していくとそんなに難しくないよ!ということです。


まあPL/SQLでループ回して書いたほうが遥かに簡単なんですけどね。

投稿

  • 回答の評価を上げる

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

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

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

  • 回答の評価を下げる

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

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

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

  • 2018/08/22 10:40

    ご回答ありがとうございました!
    無事に思い描いた通りの値を得ることができました。
    またわかりやすく経過まで書いていただき、理解を深めることができました。

    WITH句についてはまったく触れたことがなかったのですが、
    まずは最大値を取得してその後最大値+NULLの行数を取得してSETするというロジックということは理解できました。

    キャンセル

  • 2018/08/22 12:47 編集

    部品に欠番が無いなら大丈夫ですけど、あるなら駄目ですね。
    あ、こっちじゃなかった、すみません。(汗)

    キャンセル

+1

こういう感じですか?

update
    keycode_master m
set
    order_no = (select count(*) + 1 from (
        select
            *
        from
            keycode_master
    ) t
    where
        maker_name = m.maker_name
        and
        prd_number < m.prd_number
    )
where
    order_no is null
;

ちょっと手元にMySQLしかないのでOracleだったら以下でも通るかもしれません。

update
    keycode_master m
set
    order_no = (select count(*) + 1 from keycode_master
    where
        maker_name = m.maker_name
        and
        prd_number < m.prd_number
    )
where
    order_no is null
;

投稿

  • 回答の評価を上げる

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

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

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

  • 回答の評価を下げる

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

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

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

  • 2018/08/22 10:49

    ご回答ありがとうございます。
    想像通りの採番ができました!

    一点不明なのは、prd_number < m.prd_numberの不等号なのですが、
    prd_numberは品番の列のことをさしていますよね?
    品番は数値だけだったり、小数点もあったり、英語+数字だったり、バラバラなんですが、
    それでも不等号で実行できて、連番がふれたのがよく理解できません、、、

    キャンセル

  • 2018/08/22 12:49

    部品に欠番が無いなら大丈夫ですけど、あるなら駄目ですね。

    キャンセル

  • 2018/08/22 14:15

    sazi様がおっしゃるように採番用列が欠番してない前提ですね。
    prd_numberは文字列の場合、辞書順なので同じメーカ内でprd_numberが辞書順でみて
    大きくなるのであれば大丈夫です。

    自身の行よりも若い行の数を取得して、それの+1を採番するというSQLでした。

    キャンセル

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

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