別レコードと比較条件文

質問

別レコードと比較条件文
下記のテーブルがあります。
・番号1から始まり番号が2に採番されると、履歴番号に番号1が入ります。
・履歴番号はひとつ前の番号を持ちます。
・Aテーブルのレコード数は100以上あります。
Aテーブル
ID 番号　履歴番号
A 1　NULL
A 2 1
A 3 2
B 1　NULL
B 2 1
B 3 2

結果として出力させたいのは、変更回数です。
・履歴番号は実際のデータですと10桁の複雑な文字列です。
ほしい出力結果
ID 番号　履歴番号 変更回数
A 1　NULL 0
A 2 1 1
A 3 2 2
B 1　NULL 0
B 2 1 1
B 3 2 2

行いたい条件式とは、
一行目：履歴番号がNULLのときは変更回数0とする。
二行目：履歴番号が他レコードの番号と一致する場合、1を持つ。そのレコードの履歴回数がNULLなら判定終わり。
三行目：履歴番号が他レコードの番号と一致する場合、1を持つ。そのレコードの履歴番号が更に他レコードと一致する場合、2を持つ。そのレコードの履歴回数がNULLなら判定終わり。

このようにして変更回数を設定したいのですが、思いつきませんのでご教示お願いできますでしょうか。
試してみたのは、DECODE(履歴番号,'','0',番号,1)
とまでためしに作りましたが、同じレコード同士の比較になってしまうため、比較できません。

回答No.9

papapa0427 · ベストアンサー

＞一行目：履歴番号がNULLのときは変更回数0とする。
＞二行目：履歴番号が他レコードの番号と一致する場合、1を持つ。そのレコードの履歴回数がNULLなら判定終わり。
＞三行目：履歴番号が他レコードの番号と一致する場合、1を持つ。そのレコードの履歴番号が更に他レコードと一致する場合、2を持つ。そのレコードの履歴回数がNULLなら判定終わり。

と言うこと前提に結論です。ＳＱＬでは書けません。フロントエンドでの処理が必要です。

つまり、一行しかヒットしなかった場合は当然履歴番号はＮＵＬＬですね。でそのＩＤのレコード取得は終了ですね。
二行ヒットした場合は、ＮＵＬＬデータの処理と履歴を持つレコードの処理が必要になりますね。つまりここで二重作業（レコードの再検索）が必要になります。
さらに三行ヒットした場合、上記に加えて三行目の処理も必要になります。（再度のレコードの再検索）
しかしレコード数は一定ではないと推察しますので、一回のＳＱＬ文でこの様な全データを取得するのは不可能と考えます。

また常に３レコードあるとすれば何とかなりそうですが３レコード以上の場合もあるとすれば１回のＳＱＬ文でこのようなデータを取得するは不可能です。

行うとすれば、ＩＤ毎に必要な処理をさせてワークテーブルにＩＮＳＥＲＴし、更新回数を設定する。出力結果としてワークテーブルの内容を出力するという方法しか今の私には思いつきません。

回答No.8

yorozu_ya

> 同じようなSQL文は試したのですが・・エラーでした。

提示されていないものについてのコメントは出来ません。

> ・start withではなくwithなのでしょうか？

漏れましたね。
ORACLEの無い環境で回答してます。

> ・WHERE句で条件指定したい場合は、withおよびCONNECT BYはどこに入れるのでしょうか？

提示したサイトにすべて解説されています。
ページをめくって見てください。

回答No.7

yorozu_ya

考えて欲しかったけど面倒なので回答します。

SELECT ID, 番号, 履歴番号, LEVEL - 1 AS 変更回数
FROM Aテーブル
WITH 履歴番号 IS NULL
CONNECT BY PRIOR ID = ID AND PRIOR 番号 = 履歴番号;

お礼

ありがとうございます。
同じようなSQL文は試したのですが・・エラーでした。
・start withではなくwithなのでしょうか？
・WHERE句で条件指定したい場合は、withおよびCONNECT BYはどこに入れるのでしょうか？

回答No.6

yorozu_ya

> NULLのとき始まりが「1」となっております。
> ・これ(今回でいうと変更回数)を「0」始まりにするには、どのように行えばよいでしょうか。

マイナス 1 すればよろしいかと。

お礼

ありがとうございます。
階層問い合わせはWHERE句の後に記述するのでしょうか？
以下で実行するとエラーがでてしまい変更回数をセットすることができません。
色々なサイトを調べましたが、うまくできません。
select　level
from A
where *
start with A.履歴番号 is null
connect by prior A.番号 = A.履歴番号

回答No.5

noname#140925

＞業務用途です。select以外で行ってもいいのですが思い浮かびませんのでご教示ください。

そのテーブルに変更回数の項目設けておいて、新しい履歴レコードinsertする際に、現在の変更回数に1を加えた値を設定すれば良いだけですが。
何か難しく考えすぎなのでは？

お礼

ありがとうございます。
なるほど理解しましたが、今回はテーブルに項目を追加せず出力の際に付与してやる形で行いたいと思います。
下記URLの階層手順でできそうですが、
http://codezine.jp/article/detail/2694
NULLのとき始まりが「1」となっております。
・これ(今回でいうと変更回数)を「0」始まりにするには、どのように行えばよいでしょうか。
・又今回コード毎にこのような階層条件を行いたいのですが、どのように行えばよいでしょうか。

回答No.4

yorozu_ya

> どのような方法でしょうか。

キーワードが提示されたのですから、検索してみてください。
その上で分からない点を質問してください。

お礼

ありがとうございます。
検索させて頂きましたが、今回の事例でどう使っていけばよいかわかりません。
ヒントをいただけますか。

回答No.3

yorozu_ya

階層問い合わせを使えばよろしいかと。

お礼

ありがとうございます。
どのような方法でしょうか。

回答No.2

noname#140925

ANo1さんも書いていますが、端から変更回数の項目を用意しておいて、insert時に回数を入れておくべきでは？
Oracleは趣味で自宅で使用しているのでしょうか？
業務用途だと速度的な事を考えても、select時に計算で行うのは無駄以外の何物でもないと思いますが。

お礼

ありがとうございます。
業務用途です。select以外で行ってもいいのですが思い浮かびませんのでご教示ください。

回答No.1

taka451213

こんにちは。

＞履歴番号は実際のデータですと10桁の複雑な文字列です。
という事は、番号も同様ですよね？
具体的にどのような文字列なのでしょう・・・？

というか、レコード入れるときに振ればいいだけなのに・・・。

お礼

ありがとうございます。
今回はＳＱＬ文で抽出を行いたいのです。
なのでINSERTは使いません。
データを抽出するときに変更回数はオリジナルとして持たせます。

番号も履歴番号も10桁の「A3Jfj389DO」などといった文字列の組み合わせです。