副問合せを使わないでグループごとの最新日付を取得

質問

MySQL4.0.2x を使用して、検索プログラムを実装させようとしていますが、
副問合せに慣れてしまってどうSQL文を書いてよいか考えあぐねています。
何かよいやり方がありましたらご教授ください。

やろうとしていることは、以下の発行履歴テーブルを購入IDでグループ化し、そのグループ内で発行日が最新のレコードを、全カラム分引っ張ってくる。
(結果は購入IDが一意になっている)
そしてそのテンポラリーテーブルを作成することです。（後で別テーブル群と購入IDでJOINさせるため）

どうもサンプルテーブルの空白が崩れてしまって見づらくて申し訳ないですが、宜しくお願いします。

■ユニーク：参照番号
+----------+--------+------------+---------------+---------------------+---------------+--------
| 購入ID | 連番 | 商品コード | 参照番号 | 発行日 | IP 　 | ・・・ (以降略)
+----------+--------+------------+---------------+---------------------+---------------+--------
| 0001 | 1 | pen_00580 | 000000001_001 | 2008-01-01 00:11:01 | 123.45.678.90 | ・・・
| 0002 | 1 | rot_00187 | 000000002_001 | 2008-01-03 00:13:49 | 111.22.333.44 | ・・・
| 0002 | 2 | rot_00187 | 000000002_002 | 2008-01-03 00:14:25 | 111.22.333.44 | ・・・
| 0003 | 1 | ter_09523 | 000000003_001 | 2007-12-25 00:19:40 | 555.66.777.88 | ・・・
| 0004 | 1 | rot_00187 | 000000004_001 | 2007-12-28 00:20:02 | 999.88.777.66 | ・・・
| 0005 | 1 | min_00210 | 000000005_001 | 2007-12-26 00:20:10 | 543.21.098.76 | ・・・
| 0005 | 2 | min_00210 | 000000005_002 | 2007-12-30 00:30:55 | 543.21.098.76 | ・・・
| 0005 | 3 | min_00210 | 000000005_003 | 2008-01-03 00:36:30 | 543.21.098.76 | ・・・
| 0006 | 1 | rot_00187 | 000000006_001 | 2008-01-10 00:45:24 | 123.45.678.90 | ・・・
+----------+--------+------------+---------------+---------------------+---------------+--------

ちなみに自分が作成したもの

(1)第1回一時テーブル
CREATE TEMPORARY TABLE 一時テーブル1
SELECT 購入ID, max(参照番号) 参照番号, max(発行日) 発行日
FROM 発行履歴
GROUP BY 購入ID

(2)第二回一時テーブル
CREATE TEMPORARY TABLE 一時テーブル2
SELECT A.*
FROM 発行履歴 A
RIGHT JOIN
(一時テーブル1) B
ON A.参照番号=B.参照番号

回答No.1

shimix · ベストアンサー

4.0でサブクエリが使えないとなると・・

(1)いったん購入ID, max(発行日)を購入IDでグループ化して（最終目的とは別の）テンポラリテーブルに書き込む。

(2)元テーブルと(1)のテーブルを（購入IDと発行日で）結合して、一致したものだけを抽出する。

でいいと思います。

お礼

やはりテンポラリテーブル×2しかなさそうですね。。有難うございました。

回答No.3

shimix

>No.2

>参照番号でジョインしようとしていますよね？
>行を一意に定められる情報が、いくつもあるように見えるのですが？

多分「発行日が最新のレコードを、全カラム」という全カラムというのを何とかしようと試行錯誤してこうなってしまったのではないかと（あくまでも推測）。

サブクエリが使えれば購入IDごとのmax(発行日)を取り出すクエリとjoinするんでしょうけどね。サブクエリが使えなければ「そのクエリの結果をテーブルに吐き出して」そのテーブルとjoinするのが手っ取り早いとは思います。

お礼

そうですね、、サブクエリ使えないのは痛いです。。
やはりテンポラリテーブルを2回作成するしかないみたいですね・・。

実はデータ量が多く、全データ対象のクエリーを小分けにしてかけてしまうと負担が掛かりそうなので、インターフェース変更をお願いして対処した方がよいかもしれないと思えてきました。。

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

回答No.2

chukenkenkou

タイトルでは「グループごとの最新日付を取得」となっていますが、参照番号でジョインしようとしていますよね？
行を一意に定められる情報が、いくつもあるように見えるのですが？

購入ID+連番の最大値＝参照番号＝発行日が最新

なのでしょうか？

補足

>購入ID+連番の最大値＝参照番号＝発行日が最新

はい、データ上そうなっているようです。
実はこのシステム自体自分が作成したものではない上、オリジナルの作成者が捕まらないので、何件かの実データを見ての判断ですが、
私にも行を一意に定められる情報が複数あるように見えます。
なぜそうなっているのかは不明です。
まずあり得ないと思うんですが、万一購入IDグループ内で発効日が重なった場合どうしよう？、というところで安直に参照番号を選びました。