- PR -

Oracle10g 同じLot,KUBUN毎にKAISUUの最大値のデータを取得するようなSQL

1
投稿者投稿内容
ふっく
会議室デビュー日: 2007/09/16
投稿数: 3
投稿日時: 2007-09-16 11:13
いつも勉強させていただいております。

次のようなテーブルがあるとします。

Lot|　KUBUN　| KAISUU | ATAI
A　|　　1　　| 0 | 10
A　|　　1　　| 1 | 20
A　|　　2　　| 0 | 30
A　|　　3　　| 0 | 40
A　|　　3　　| 1 | 50
A　|　　3　　| 2 | 60
B　|　　1　　| 0 | 70
B　|　　2　　| 0 | 80
B　|　　2　　| 1 | 90
B　|　　2　　| 2 | 100
B　|　　2　　| 3 | 110

このテーブルから、同じLot、KUBUN毎にKAISUUの最大値のデータを取得するようなSQL、
どのように書けばよいでしょうか？以下のような取得結果です。

Lot|　KUBUN　| KAISUU | ATAI
A　|　　1　　| 1 | 20
A　|　　2　　| 0 | 30
A　|　　3　　| 2 | 60
B　|　　1　　| 0 | 70
B　|　　2　　| 3 | 110

色々やっていますが、上手くいきません。

お手数をおかけしますが、
どなたか、知恵をお貸下さい。
よろしくお願いいたします。
dodo
ベテラン
会議室デビュー日: 2004/05/12
投稿数: 99
お住まい・勤務地: 東京都渋谷区
投稿日時: 2007-09-16 16:06
他にも書き方はあると思いますが、こんなSQLではどうでしょう？

1 select a.* from TEST a,
2 (select LOT, KUBUN, MAX(KAISUU) as KAISUU from TEST group by (LOT, KUBUN)) b
3 where a.LOT=b.LOT and a.KUBUN=b.KUBUN and a.KAISUU=b.KAISUU
SQL> /

LOT KUBUN KAISUU ATAI
-------- -------- ---------- ----------
A 1 1 20
A 2 0 30
A 3 2 60
B 1 0 70
B 2 3 110


[ メッセージ編集済み 編集者: dodo 編集日時 2007-09-16 17:44 ]
とんくま
ベテラン
会議室デビュー日: 2005/08/02
投稿数: 56
お住まい・勤務地: 東京
投稿日時: 2007-09-16 21:54
10g なら ROW_NUMBER() 関数が使えるはずですので、以下でも可能かと思います。
コード:
SELECT LOT, KUBUN, KAISUU, ATAI

  FROM (SELECT t.*

             , ROW_NUMBER() OVER(PARTITION BY LOT, KUBUN ORDER BY KAISUU DESC) n

          FROM TEST t

       )

 WHERE n = 1;


[ メッセージ編集済み 編集者: とんくま 編集日時 2007-09-16 21:56 ]
ふっく
会議室デビュー日: 2007/09/16
投稿数: 3
投稿日時: 2007-09-16 23:03
dodo様、とんくま様

早速のご返信ありがとうございます！
様々な方法があり大変勉強になります。

追加でご教授いただきいたいことがあります。
上記のテーブル（TEST)を次のような形で
他のテーブル(TEST1,TEST2)と結合させたいと考えております。

TEST1テーブル
Lot|zyunban
A　|　　1　　
B　|　　2　　

TEST2テーブル
Lot| No
A　|　100　
B　|　200　

求めたい取得結果は以下の通りです。

Lot|　KUBUN | KAISUU | ATAI | zyunban | No
A　|　　1　　| 1 | 20 | 1 |100
A　|　　2　　| 0 | 30 | 1 |100
A　|　　3　　| 2 | 60 | 1 |100
B　|　　1　　| 0 | 70 | 2 |200
B　|　　2　　| 3 | 110 | 2 |200

dodo様やとんくま様に教えていただいた方法に
結合を加えてみたのですが（上手く説明できなくて申し訳ありません）
どうもうまく処理されません。
Fabulous
ベテラン
会議室デビュー日: 2007/08/11
投稿数: 51
投稿日時: 2007-09-17 00:56
引用:

dodo様やとんくま様に教えていただいた方法に
結合を加えてみたのですが（上手く説明できなくて申し訳ありません）
どうもうまく処理されません。

説明の仕方が逆ですね。
結合をどのように加えたのかはSQLズバリをここに書けばよいです。

逆にどういうデータからどういう結果を求めるかの方はご提示の例ではわかりません。
・「同じLot、KUBUN毎にKAISUUの最大値のデータ」が複数あったらどうします？
・TEST1テーブルに一致するLotのデータが複数または存在しなかったら？
・TEST2テーブルに一致するLotのデータが複数または存在しなかったら？

例が都合がよすぎて仕様が見極めにくいです。
とんくま
ベテラン
会議室デビュー日: 2005/08/02
投稿数: 56
お住まい・勤務地: 東京
投稿日時: 2007-09-17 05:06
例からは、zyunban と No は 元々の LOT と マッチ した値を取るように見えます。
この推測が当たっていれば、以下の SELECT文で求める結果が得られると思います。
コード:

SELECT t0.LOT, KUBUN, KAISUU, ATAI, zyunban, No

  FROM (SELECT t.*

             , ROW_NUMBER() OVER(PARTITION BY LOT, KUBUN ORDER BY KAISUU DESC) n

          FROM TEST t

       ) t0

       INNER JOIN

       TEST1 t1

         ON  t1.lot = t0.lot

       INNER JOIN

       TEST2 t2

         ON  t2.lot = t0.lot

 WHERE n = 1

 ORDER BY

       t0.LOT, KUBUN

;

Fabulousさんの指摘された例外的データについては、それぞれ多少のコードの追加で対応できるように思えます。

[ メッセージ編集済み 編集者: とんくま 編集日時 2007-09-17 05:09 ]
ふっく
会議室デビュー日: 2007/09/16
投稿数: 3
投稿日時: 2007-09-17 15:04
Fabulous様
ご指摘ありがとうございます。
私の説明不足で申し訳ありません。
以後、気を付けてさせていただきますので、
ご勘弁ください。

ご質問をいただいた件について、回答させていただきます。
・「同じLot、KUBUN毎にKAISUUの最大値のデータ」が複数あったらどうします？
→複数はない前提でございます。
・TEST1テーブルに一致するLotのデータが複数または存在しなかったら？
→複数または存在しないことはない前提でございます。
・TEST2テーブルに一致するLotのデータが複数または存在しなかったら？
→複数または存在しないことはない前提でございます。


とんくま様
ご回答ありがとうございます。
とんくま様のご推測の通り、
回答いただいたSQLで求めることができました。

初心者の説明にもかかわらず
適切なご回答、本当にありがとうございました。

大変勉強になりました。
また、質問させていただくこともあろうかと思いますが、
よろしくお願い致します。
1

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