SQL文の構築について教えてください。

SQL文の構築について教えてください。
こんばんは。Oracle9iのデータベース検索で、どうしてもSQLが思い浮かばないため、質問させていただきます。

■Table
ID　　　 NUM　　　SIGN
100　　　1　　　　A
100　　　2　　　　A
101　　　1　　　　A
102　　　1　　　　B
103　　　1　　　　B
104　　　1　　　　C

首キーはID、NUMです。
上記のようなテーブルで、
IDが同一かつNUMが最大のもので、
SIGNが同一のレコードで、2件以外のレコードを抽出したいです。
（例表でいうと、ID104のレコードのみが抽出されるようにしたい）

すこし急ぎなんですが、SQLが得意でお時間がある方、力を貸していただけないでしょうか。
よろしくお願い致します。

No.6 ベストアンサー 回答者： taka451213 回答日時： 2010/04/28 17:06

こんにちは

SELECT ID, NUM, SIGN
FROM
(SELECT ID, NUM, SIGN, COUNT(ID) OVER(PARTITION BY SIGN) AS SIGN_COUNT
-- ここでSIGNの数を数える
FROM
(SELECT ID, NUM, MAX(NUM) OVER(PARTITION BY ID) AS MAX_NUM, SIGN
-- 後ではじくために、NUMとMAX(NUM)を取得
FROM Table)
WHERE NUM = MAX_NUM
-- NUMが最大のものだけ抽出
)
WHERE SIGN_COUNT != 2
-- ここで初めてSIGNの件数で抽出

考え方はこれでいいと思いますが、SQL的にチューニングの余地はかなりありますね・・・。
（もっといい書き方があると思います）
何しろ今Oracleが手元にないもので・・・。
テストしてません^^;

この回答へのお礼

どうもありがとうございます。
休暇に入ってしまったので、デバッグをすることはできませんが、
また試してみます。

お礼日時：2010/04/30 16:51

No.5 回答者： taka451213 回答日時： 2010/04/28 15:33

あぁ・・・、何となく意味がわかったような気が・・・。

一発目にSIGNをカウントするから駄目なのね？
まずIDごとのMAX(NUM)レコードだけ引っ張って、それからSIGNを数えるって事？

あとひとつわかりません・・・。
（これによってSQLが根本的に変わってきます）
今は
ID　　　 NUM　　　SIGN
100　　　1　　　　A
100　　　2　　　　A
だけど、
ID　　　 NUM　　　SIGN
100　　　1　　　　A
100　　　2　　　　B
とかってあるんですか？

もしあったら、100,1,Aは無視？
それともSIGNが違うから、検索対象になり得る？？？

この回答への補足

＞まずIDごとのMAX(NUM)レコードだけ引っ張って、それからSIGNを数えるって事？
その通りです。説明不足で申し訳ありません。

同じIDのSIGN違いの場合もあります。
その場合も、IDごとの最大のNUMを扱いたいです。

＞もしあったら、100,1,Aは無視？
無視でOKです。扱いたいのは同じIDの中でNUMが最大の「100,2,B」です。

補足日時：2010/04/28 16:31

No.4 回答者： taka451213 回答日時： 2010/04/28 13:50

こんにちは

すみません・・・、捕捉で結構ですので、もう少し整理しましょう。
結局、
ID　　　NUM　　SIGN
100　　　1　　　A
100　　　2　　　A
101　　　1　　　A
102　　　1　　　B
103　　　1　　　B
104　　　1　　　C
104　　　2　　　C ←（今回追加）
とあった場合に抽出する対象はどのレコードですか？
できればその条件も・・・。

この回答への補足

こんにちは。お世話になります。

抽出結果は、「104 2 C」のレコードのみを期待しています。

条件は、
IDが同一かつNUMが最大のもので、SIGNが同一のレコードで、
検索結果が2件以外のレコードを抽出したいです。

IDが「104」のものは2件ありますが、NUMが最大のレコードは「104 2 C」なりますので、
SIGNが「C」のレコードは検索結果1件とします。
SIGN「A」「B」はそれぞれ検索結果2件なので、最終的な結果からは対象外となり、
SIGN「C」は検索結果が2件以外（1件）なので、最終的な結果の対象にします。

すみませんが、宜しくお願い致します。

補足日時：2010/04/28 14:56

No.3 回答者： taka451213 回答日時： 2010/04/27 19:44

こんばんは

＞テーブルに 「104,2,C」をINSERTすると・・・
だって、「104,1,C」もあるんですよね？
それだったら「100,2,A」も出てこなくちゃなりませんが・・・？

＞SIGNが同一のレコードで、2件以外のレコードを抽出したいです。
最初の要件を満たしていないです。

この回答への補足

私の表現が悪かったようです。すみません。

＞IDが同一かつNUMが最大のもので、
＞SIGNが同一のレコードで、2件以外のレコードを抽出したいです。
最終的な抽出は、同一IDはNUMが最大のもののみを1件分としてカウントをしたいのです。

今日も時間があけばもう少し考えてみようと思います。

補足日時：2010/04/28 02:24

No.2 回答者： taka451213 回答日時： 2010/04/27 12:45

あちゃー

COUNT(列名、列名)は駄目ですね・・・。
IDだけでよかったです。
失礼しました・・・。

この回答へのお礼

本日デバッグ致しました。
最後のWhere条件を　SIGN=1　を　SIGN<>2　に変更したところ、うまく抽出できました。
本当にありがとうございます。

ただ1点だけ問題がありまして、
テーブルに 「104,2,C」をINSERTすると、私の思いは「104,2,C」のみを抽出したいのですが、
抽出件数0件になってしまいます。
同じIDの場合は、NUMの最大値で抽出したいのですが、
DISTINCTやGROUP BYで試しましたが、うまくいきませんでした・・・

もしよろしかったらヒントを頂けますでしょうか。

お礼日時：2010/04/27 19:07

No.1 回答者： taka451213 回答日時： 2010/04/25 23:33

こんばんは

えーと、取り急ぎという事で、未検証です・・・。
SELECT ID, NUM, SIGN
FROM (SELECT ID, MAX(NUM) OVER(PARTITION BY ID) AS NUM, COUNT(ID, NUM) OVER(PARTITION BY SIGN) AS SIGN
FROM Table)
WHERE SIGN = 1

この回答へのお礼

早いご回答ありがとうございます。
そのまま実行してみたところ、COUNTのところでエラーが出てしまいました。

しかし、OVER、PARTITION BY というやり方は初めて見ました。
分析関数というものなんですね。使えそうな感じがするのでいろいろ改造してみようと思います。
ありがとうございました。

お礼日時：2010/04/27 00:57