【SQL】1つのレコードを分割して複数のレコードを作成する方法を教えて下さい。


次のように各レコードに対して数量を記録したテーブルがあります。

商品コード 数量
001   25
002   50

このテーブルから、1つのレコードに対する数量を一定数(たとえば20)以下に制限し、それを越える場合はレコードを分割した次のようなテーブルを作成したいです。

商品コード 数量
001_1 20
001_2 5
002_1 20
002_2 20
002_3 5

こうした変換をSQLのみで行うことは可能でしょうか? 環境としてはMS-ACCESSを考えていますが、一般的なSQLの話で構いません。

欲を言えば、分割後のレコードのコードが上記のように「分割前のレコードのコード+枝番」というようになっているとなお良いです。

よろしくお願いします。

回答の条件
  • 1人5回まで
  • 登録:2007/05/19 22:12:24
  • 終了:2007/05/24 01:38:46

ベストアンサー

id:memo77 No.1

memo77回答回数238ベストアンサー獲得回数202007/05/20 00:35:35

ポイント60pt

単一テーブルだと思いつきませんね。

もし別テーブルを用意しておいてよいのであれば

[T_DUMMY]

[ID] [KEY] [ODD]

1-1 1 1

2-1 2 0

2-2 2 1

3-1 3 0

3-2 3 0

3-3 3 1

4-1 4 0

4-2 4 0

4-3 4 0

4-4 4 1

5-1 5 0

5-2 5 0

5-3 5 0

5-4 5 0

5-5 5 1

...

のようなテーブルを想定される総数を満たすように用意してやります。で、

SELECT T1.ID,T1.KEY,T2.IIF([ODD]=0,INT([数量計]/20),([数量計] MOD 20))

FROM [T_DUMMY] AS T1

INNER JOIN

(SELECT 商品コード,SUM([数量]) AS 数量計,INT(SUM([数量])/20)+1 AS KEY FROM [テーブル]) AS T2

ON T1.KEY=T2.KEY

という感じでどうでしょう。

ヘルプ見ないで書いてるので、AccessでMOD使えたっけとか、サブクエリ使えたっけというのはすみませんが調整してください。

考え方の参考です。

まあ、どうしてもこの処理をSQL一発でやれといわれたら、SQLServweでならカーソル、Accessでならレコードセット使います。

id:you1982

ありがとうございます。別テーブルを作成するのが少々、面倒ですが、想定される数量がそんなに大きくない場合は実用になりそうですね。

ちなみに1行目の

IIF([ODD]=0,INT([数量計]/20),([数量計] MOD 20))

の部分は、もしかしたら

IIF([ODD]=0,20,([数量計] MOD 20))

の方が正しいでしょうか?

2007/05/20 04:05:08
  • id:you1982
    自己レスです。

    上記の例だけに限って言えば、数量が0~20、21~40、40以上のデータを抽出しそれらをUNIONでくっつければOKですが、もちろん知りたいのはそういうことではなく、もっと汎用的な方法を知りたいです。
  • id:memo77
    >の部分は、もしかしたら
    >IIF([ODD]=0,20,([数量計] MOD 20))
    >の方が正しいでしょうか?

    ああ、そうです(^-^;

    すみません。昨夜結構朦朧(というか泥酔)した状態で書いてたみたいです。
    こういう小細工をするよりは普通にレコードセット使うか、動的にSQL生成するほうがいいですよね。
  • id:you1982
    お返事、ありがとうございました。

    早速、使ってみました。レコードの分割方法が複雑な条件になる場合はSQL文の記述が厄介なことになりますが、単純に単位数量が1の場合、つまり「数量」フィールドの数分レコードを複製したいような場合は結構、便利ですね。一度、T_DUMMYのようなテーブルを作っておけば使い回せますし。使えるテクニックとして覚えておきたいと思います。

    あと、レコードセットを使う方法というのはどのようなことでしょうか?ADOでプログラミングする、ということでしょうか?
  • id:memo77
    >>まあ、どうしてもこの処理をSQL一発でやれといわれたら、SQLServweでならカーソル、Accessでならレコードセット使います。

    言い回しが変...orz
    レコードセット使うというのはSQL一発じゃないですね。

    >あと、レコードセットを使う方法というのはどのようなことでしょうか?ADOでプログラミングする、ということでしょうか?

    DAO(ADO)使ってモジュールで処理するという意味です。
    Dlookupで最大値拾って動的にUNION生成するほうがレコードセット使うより楽で速いか。

    For i=0 to int(maxValue/20)
    strSQL=strSQL & " UNION ... WHERE [数量計] > " & int(maxValue/20) & vbCrLf
    Next

    みたいな。
    「SQL一般の話」ということであればカーソルでほぼ同じ処理が書けます。
  • id:you1982
    たびたびありがとうございます。そういう意味でしたか。了解です。
  • id:kn1967
    『一箱に20個入るので50個の注文があった場合は箱3つが必要になり
    それぞれの箱には通し番号付きの出荷伝票を貼らなければならない』
    といったような場合の事だとおもいますが、処理系に依らない汎用的な方法となれば
    既に回答いただいているように別テーブルを用意しておくという方法がポピュラーでしょう。

    別の手段として、アプリケーション側での対処、例えばAccessの場合であれば
    『レポートの詳細セクションにて必要回数分だけカウントしながら印刷』
    とったような実装をしたりもします。

    何のために今回の質問を発する事になったのかは存じませんが、
    SQLだけを利用して単純に汎用性のある結果を求めたいのであれば
    SELECT 商品コード, -int(-数量/20) AS 分割数, 数量 Mod 20 AS 端数 FROM テーブル;
    で求められる
    商品コード 分割数 端数
    001   2   5
    002   3   5
    で必要十分な場合もあるとは思いますので質問を発する事になった経緯を逆に辿ってみてください。
  • id:you1982
    コメント、ありがとうございます。

    状況はご指摘の通りです。質問の経緯は好奇心というのも大きいのですが(笑)、簡単に言うとある一定値以上の数量を与えるとエラーになってしまうアプリに喰わせるテーブルを作成したいということです。しかも、できればそうした変換操作を明示的に行う必要がないようにクエリー一発でやりたい、という理由です。

    memo77さんのご回答で実用上、十分かと思われますし、他に回答が出るとも思えないのですが、頭の体操的な好奇心もありますのでもう少し続けさせて下さい。

この質問への反応(ブックマークコメント)

「あの人に答えてほしい」「この質問はあの人が答えられそう」というときに、回答リクエストを送ってみてましょう。

これ以上回答リクエストを送信することはできません。制限について

絞り込み :
はてなココの「ともだち」を表示します。
回答リクエストを送信したユーザーはいません