- PR -

通し番号の抜け番

1|2 次のページへ»
投稿者投稿内容
yuki
会議室デビュー日: 2004/07/28
投稿数: 6
投稿日時: 2004-12-21 14:03
こんにちは。

Oracle8iを使っています。
通し番号の入るカラムを持つテーブルにINSERTする時、
すでにあるデータの抜け番に割り振ることはできるでしょうか？

（例）
[会社コード,通し番号,名前]
A,1,山田
A,2,山本
A,4,川崎
A,5,川上
→ここにレコードをINSERTするとき
通し番号には自動的に抜けている番号のうち
最小のものを割り振りたい。（例では"3")

最初からDBで扱えればよかったのですが、
既存のテキストデータを移行させてきたため、このような事態になってしまいました。

お知恵を貸していただけますでしょうか。
よろしくお願いします。

[ メッセージ編集済み 編集者: yuki 編集日時 2004-12-21 14:12 ]
はにまる
ぬし
会議室デビュー日: 2003/12/19
投稿数: 969
お住まい・勤務地: 誤字脱字の国
投稿日時: 2004-12-22 00:47
話の内容を限定した制約とすれば問題無く出来ますよ。

１．通し番号を１から順にレコードが存在するか検索する。
２．レコードが存在しないならば、その番号を抜け番として保持する。
３．新規レコードの通し番号に抜け番を適用する。

そんな事、聞いちゃ〜いね〜よ！と思われるんでしょうが
であれば、もう少し制約をプリーズって感じですね。

同時更新は存在するのか？
テーブルレイアウトの変更や追加は出来ないのか？
要求される処理速度は？
仕事上における仕様の権限は？
等など
かつのり
ぬし
会議室デビュー日: 2004/03/18
投稿数: 2015
お住まい・勤務地: 札幌
投稿日時: 2004-12-22 09:28
OracleのSQLはあまり詳しくないので、概念だけ。
コード:
select
	通し番号
from
	(select
		t2.通し番号
	from
		table t1,
		table t2
	where
		t1.通し番号 = t2.通し番号 + 1 (+) and
		t1.通し番号 is null)
having
	通し番号 = min(通し番号)


table1.NO
---------
        1
        2
        4
        5

t1.NO  t2.NO                      t1.NO  t2.NO
-----  -----                      -----  -----
    1      2                          1      null
    2      3  ---> 外部結合 -->       2      2
    4      5                       null      3
    5      6                          4      null
                                      5      5
                                   null      6

t1がnullのレコードを抽出

t1.NO  t2.NO
-----  -----
 null      3
 null      6

t2が最小のレコードを抽出

t1.NO  t2.NO
-----  -----
 null      3

こんな感じで抽出できますので、insertのselect句にこのサブクエリを
使用して１つのSQLで完結することが可能だと思います。
ちなみに上記例の場合、[3]が使用されれば[6]が抽出されますので、
特に「穴埋め」と「最大 + 1」との処理を分ける必要もありません。

ただし、はにまるさんの指摘どおり、もっと情報がないと
適切な回答は難しいですね。

cucsna
会議室デビュー日: 2003/12/06
投稿数: 2
投稿日時: 2004-12-22 18:08
動作確認などしていないので、自信ないのですが・・・。

select MAX(通し番号)
FROM
　(select 通し番号 from テーブル名
　　order by 通し番号)
WHERE 通し番号 = ROWNUM;

というSQL文で、もしかして抜け番の手前の番号が出てくるのではないかと思います。
このselect文の結果に１を足した値が、最小の抜け番になる？かなと・・・。
ただ、FROM句で使っている副問い合わせは、FULL検索になるので、処理は重くなるかもしれません。

役に立たない回答だったらすみません。
yuki
会議室デビュー日: 2004/07/28
投稿数: 6
投稿日時: 2004-12-22 20:00
はにまるさん、かつのりさん、ご返信ありがとうございます。
また、情報不足で申し訳ありませんでした。
とりあえず追加情報を書きます。


  • インターフェースはWEB（JSP+Servlet）
  • 同時更新はない（APでロック制御）
  • テーブルレイアウトの変更、カラムの追加も可能
  • Oracle自体のチューニングも可能です
  • 1度にINSERTするレコードは最大500行程度
  • テーブル自体のレコード数は5000〜6000行程度
  • 処理速度は（とりあえず回線速度等は無視して）この処理のみで
    　1分以内を目標にしています
  • DBサーバの負荷を押さえたいので、なるべくSQL発行回数を
    　少なくしたいです

引用:

はにまるさんの書き込み (2004-12-22 00:47) より:
話の内容を限定した制約とすれば問題無く出来ますよ。

１．通し番号を１から順にレコードが存在するか検索する。
２．レコードが存在しないならば、その番号を抜け番として保持する。
３．新規レコードの通し番号に抜け番を適用する。

ありがとうございます。
これはプログラム側で処理するんですよね？

引用:

かつのりさんの書き込み (2004-12-22 09:28) より:
OracleのSQLはあまり詳しくないので、概念だけ。
コード:

select

	通し番号

from

	(select

		t2.通し番号

	from

		table t1,

		table t2

	where

		t1.通し番号 = t2.通し番号 + 1 (+) and

		t1.通し番号 is null)

having

	通し番号 = min(通し番号)


このSQLを試したのですが、

コード:

	t1.通し番号 = t2.通し番号 + 1 (+) and

の部分で、外部結合が引っかかり「右カッコがありません」エラーに
なってしまいました。

そこで
コード:

	t1.通し番号 - 1 = t2.通し番号(+) and

でなんとかならないかなあ、と試行錯誤しましたが
これだと最小値が使えませんよね。

引用:

ちなみに上記例の場合、[3]が使用されれば[6]が抽出されますので、
特に「穴埋め」と「最大 + 1」との処理を分ける必要もありません。

それにこのせっかくの最大値取得も、使えないですし…

ただ、色々やっているうちに説明していただいた概念は
大分わかってきましたので、もう少しがんばってみます。

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

[ メッセージ編集済み 編集者: yuki 編集日時 2004-12-22 20:03 ]
yuki
会議室デビュー日: 2004/07/28
投稿数: 6
投稿日時: 2004-12-22 20:15
cucsnaさん、ご返信ありがとうございました。

引用:

書き込み (2004-12-22 18:08) より:
動作確認などしていないので、自信ないのですが・・・。

select MAX(通し番号)
FROM
　(select 通し番号 from テーブル名
　　order by 通し番号)
WHERE 通し番号 = ROWNUM;

というSQL文で、もしかして抜け番の手前の番号が出てくるのではないかと思います。
このselect文の結果に１を足した値が、最小の抜け番になる？かなと・・・。
ただ、FROM句で使っている副問い合わせは、FULL検索になるので、処理は重くなるかもしれません。

役に立たない回答だったらすみません。

このSQLでうまくいきました。
おっしゃるとおり抜け番の手前の番号が取得できるので、
コード:
select MAX(通し番号) +1
FROM
　(select 通し番号 from テーブル名
　　order by 通し番号)
WHERE 通し番号 = ROWNUM;

で、目的の抜け番を取ろうと思います。
サブクエリー内の検索については、INDEXを使うなどで
FULL SCANにならないように工夫してみます。

ありがとうございました。
めだか
大ベテラン
会議室デビュー日: 2004/11/11
投稿数: 109
投稿日時: 2004-12-27 11:26
SQL Serverでも使えないかと思い かつのりさんのSQL文を
実行してみたのですが、外部結合がうまくいきません

select
t2.通し番号
from
table t1,
table t2
where
t1.通し番号 = t2.通し番号 + 1 (+) and
t1.通し番号 is null
この部分を
select t2.pkey1 from t0001 t1,t0001 t2
where t1.pkey1 *= t2.pkey1+1
と変更し、
t1.NO t2.NO t1.NO t2.NO
----- ----- ----- -----
1 2 1 null
2 3 ---> 外部結合 --> 2 2
4 5 null 3
5 6 4 null
5 5
null 6

この外部結合を作りたいのですが、右外部結合でも左外部結合でも
t1.no
------
1
2
4
5
のみしかselectできないようです
SQL Serverではできないのでしょうか？
Jitta
ぬし
会議室デビュー日: 2002/07/05
投稿数: 6267
お住まい・勤務地: 兵庫県・海手
投稿日時: 2004-12-28 21:04
引用:

めだかさんの書き込み（2004-12-27 11:26）より：

select
t2.通し番号
from
table t1,
table t2
where
t1.通し番号 = t2.通し番号 + 1 (+) and
t1.通し番号 is null

たいていのDBに共通して使えます。

SELECT MIN(T2.通し番号) + 1 FROM TABLE t1
FULL JOIN TABLE t2 ON t1.通し番号 = t2.通し番号 + 1
WHERE t1.通し番号 IS NULL

最小がない場合は検出できないため、０のレコードを置き、これは削除不可能にしておくこと。

どこかで一度書いたような気がするんだけどなぁ？
1|2 次のページへ»

スキルアップ／キャリアアップ（JOB@IT）