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 · ベストアンサー

こんにちは

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が手元にないもので・・・。
テストしてません^^;

お礼

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

回答No.5

taka451213

あぁ・・・、何となく意味がわかったような気が・・・。
一発目に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」です。

回答No.4

taka451213

こんにちは

すみません・・・、捕捉で結構ですので、もう少し整理しましょう。
結局、
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件）なので、最終的な結果の対象にします。

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

回答No.3

taka451213

こんばんは

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

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

補足

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

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

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

回答No.2

taka451213

あちゃー

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

お礼

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

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

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

回答No.1

taka451213

こんばんは

えーと、取り急ぎという事で、未検証です・・・。
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 というやり方は初めて見ました。
分析関数というものなんですね。使えそうな感じがするのでいろいろ改造してみようと思います。
ありがとうございました。