特定条件での連番の振り方を教えて下さい

質問

以下のようなテーブルがあります。

納品データ
注文番号　売上番号　連番　商品名
11111　　 　2　　　　　　1　　 さしすせそ
11111　　 　2　　　　　　2　　 たちつてと　
11111　　 　3　　　　　　1　　 なにぬねの
11111　　 　4　　　　　　1 　　はひふへほ

売上データ(既存データ)
注文番号　売上番号　連番　商品名
11111　 　 1　　　　　　 1　　 あいうえお
11111　 　 1　　　　　　 2　　 かきくけこ

注文番号は注文毎に付与される番号
売上番号は発送毎に付与される番号
連番は売上番号毎に付与される連番

毎回 納品データを売上データに取り込みます。

連番に関しては、
納品データと売上データで連番は連動しておらず、
売上データに納品データと同等の注文番号があれば、
注文番号の最大連番数を+1した番号で取り込みます。

更新後、売上データは、
注文番号　売上番号　連番　商品名
11111　　　 1　　　　　　 1　　 あいうえお(既存データ)
11111　　　 1　　　　　　 2　　 かきくけこ(既存データ)
11111　　　 2　　　　　　 3　　 さしすせそ
11111　　　 2　　　　　　 4　　 たちつてと
11111　　　 4　　　　　　 5　　 なにぬねの
11111　　　 5　　　　　　 6　　 はひふへほ

としたいのです。

SELECT
(SELECT 納品データ.連番+(SELECT NVL(MAX(売上データ.連番),0) FROM 売上データ
WHERE 売上データ.注文番号 = 納品データ.注文番号) AS 連番)
FROM 納品データ

で売上データの連番+1はできたのですが、
売上番号が違う場合は、連番が1になるパターンとなり、

納品データ
注文番号　売上番号　連番　商品名
11111　　　 2　　　　　　 1　　 さしすせそ
11111　　　 2　　　　　　 2　　 たちつてと　
11111　　　 3　　　　　　 1　　 なにぬねの　←
11111　　 　4　　　　　　 1 　　はひふへほ ←

上記SQLですと、
更新後、売上データは、

注文番号　売上番号　連番　商品名
11111　　　 1　　　　　　 1　　 あいうえお(既存データ)
11111　　　 1　　　　　　 2　　 かきくけこ(既存データ)
11111　　　 2　　　　　　 3　　 さしすせそ
11111　　　 2　　　　　　 4　　 たちつてと
11111　　 　4　　　　　　 3　　 なにぬねの ← 5にしたい
11111　　　 5　　　　　　 3　　 はひふへほ ← 6にしたい

となり、連番となりません。

どのようにSQLを書けばよいのか、困っています。
どうか、ご教示お願い致します。

回答No.1

yamada_g · ベストアンサー

こういうことでしょうか。

insert into 売上データ (
注文番号, 売上番号, 連番, 商品名
)
select
注文番号
, 売上番号
, ROW_NUMBER() over(partition by 注文番号 order by 売上番号, 連番) + (select COUNT(*) from 売上データ T2 where T1.注文番号 = T2.注文番号) 連番
, 商品名
from 納品データ t1;

ROW_NUMBER()で納品データ内での連番を振って、その連番に既存データの件数を足しています。
MAX(連番)だと、同一注文番号が既存データに存在しない時にNULLになってしまうので、COUNTで件数を取るようにしています。

お礼

>>ROW_NUMBER() over(partition by 注文番号 order by 売上番号, 連番)

>>MAX(連番)だと、同一注文番号が既存データに存在しない時にNULLになってしまうので、COUNTで件数を取るようにしています。

バッチリです！
ROW_NUMBERを使用するのですね。
とても勉強になりました。

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