同一テーブル内での比較（最新データによる比較）

質問

大変申し訳ありません。本日も質問させてもらったのですが、一部情報（履歴）が不足していて追加で質問させてください。
以下のようなテーブルがあり、価格が異なるところを差額一覧を出したいと思っております。
（先ほどは2名の方に回答頂き、すみませんベスト１０の設定がわからず設定できなくて、すみませんでした。）

【テーブル構成】
商品ID、商店ID、価格（円）、履歴

というテーブルがあり、

商品A、商店１、1000、1
商品A、商店２、1500、1
商品A、商店３、800、1
商品A、商店１、2000、2
商品A、商店２、2500、5
商品A、商店３、1800、3
商品B、商店１、2000、1
商品B、商店２、1700、1
商品B、商店３、2600、1
商品B、商店１、2500、4
商品B、商店２、2000、6
商品B、商店３、2800、7
・・・（多くの商品データがあります）

というデータが格納されております。
商品と商店の区分ごとに履歴の最も大きいものを取り出し　（←追加）
商店１を基準に、商品毎に商店２、３の価格差を大きい順にソートして出したいと考えています。
SQL分ではどのように記載すれば良いでしょうか？

（実際に比較したデータは以下を抽出し）【履歴の古いのは無視】
商品A、商店１、2000、2
商品A、商店２、2500、5
商品A、商店３、1800、3
商品B、商店１、2500、4
商品B、商店２、2000、6
商品B、商店３、2800、7
・・・（多くの商品データがあります）

（今回の場合の希望出力が以下のような感じです。）
価格差、商品名、商店　（←綱目です。）
800、商品B、商店３
500、商品A、商店２
-200、商品A、商店３
-500、商品B、商店２

ご教授頂けると幸いです。
ちなみに、教えて頂いたSQLをもとに以下のようにやってみましたが、うまくいきませんでした。。。
全くよくわかっていません。少し解説を入れて頂けると幸いです。
------------------------------------
SELECT
a.価格 - b.価格 AS 価格差,
a.商品ID AS 商品名,
a.商店ID AS 商店
FROM 対象テーブル a
INNER JOIN 対象テーブル b ON (a.商品ID = b.商品ID AND b.商店ID = '商店１')
WHERE a.商店ID <> '商店１'
AND a.履歴 = (SELECT MAX(a.履歴)) AND b.履歴 = (SELECT MAX(b.履歴)) 【←追加】
ORDER BY a.価格 - b.価格 DESC;
----------------------------------------
select
t2.価格-t1.価格 as 価格差
,t1.商品名
,t1.商店
from テーブル1 as t1
left join
(
select 価格,商品 from テーブル1 where 商店=商店1
) as t2 on t1.商品 = t2.商品
where t1.商店<>商店1
AND a.履歴 = (SELECT MAX(a.履歴)) AND b.履歴 = (SELECT MAX(b.履歴)) 【←追加】
ORDER BY t2.価格 - t1.価格 DESC;

回答No.3

root139 · ベストアンサー

商品B－商店３の価格差は 2800 - 2500 なので300になる気がしますが・・・。

それで良いのなら、下記の様なSQLでよろしいかと。

----------------------------------------
SELECT
a.価格 - b.価格 AS 価格差,
a.商品ID AS 商品名,
a.商店ID AS 商店
FROM 対象テーブル a
INNER JOIN 対象テーブル b ON (a.商品ID = b.商品ID AND b.商店ID = '商店１')
WHERE a.商店ID <> '商店１'
AND a.履歴 = (SELECT MAX(履歴) FROM 対象テーブル c WHERE a.商店ID = c.商店ID AND a.商品ID = c.商品ID)
AND b.履歴 = (SELECT MAX(履歴) FROM 対象テーブル d WHERE b.商店ID = d.商店ID AND b.商品ID = d.商品ID)
ORDER BY a.価格 - b.価格 DESC;
----------------------------------------

a, b, に対して、それぞれ、商店IDと商品IDが同じものの中で履歴が最大の行だけを抽出しています。
同じ事は EXISTS 句を使っても表現できます。

----------------------------------------
SELECT
a.価格 - b.価格 AS 価格差,
a.商品ID AS 商品名,
a.商店ID AS 商店
FROM 対象テーブル a
INNER JOIN 対象テーブル b ON (a.商品ID = b.商品ID AND b.商店ID = '商店１')
WHERE a.商店ID <> '商店１'
AND NOT EXISTS (SELECT * FROM 対象テーブル c WHERE a.商店ID = c.商店ID AND a.商品ID = c.商品ID AND a.履歴 < c.履歴)
AND NOT EXISTS (SELECT * FROM 対象テーブル d WHERE b.商店ID = d.商店ID AND b.商品ID = d.商品ID AND b.履歴 < d.履歴)
ORDER BY a.価格 - b.価格 DESC;
----------------------------------------

こっちは a, b, に対して、それぞれ、商店IDと商品IDが同じでそれより履歴が大きな行が存在しないものだけを抽出しています。
結果として、商店IDと商品IDが同じものの中で履歴が最大の行を取出す事になり、前のSQLと同じ意味になります。

また、サブクエリーで絞り込んでしまい、それを INNER JOIN する方法も有りますね。

----------------------------------------
SELECT
a.価格 - b.価格 AS 価格差,
a.商品ID AS 商品名,
a.商店ID AS 商店
FROM
(
SELECT * FROM 対象テーブル c
WHERE 商店ID <> '商店１'
AND NOT EXISTS (SELECT * FROM 対象テーブル d WHERE c.商店ID = d.商店ID AND c.商品ID = d.商品ID AND c.履歴 < d.履歴)
) a
INNER JOIN
(
SELECT * FROM 対象テーブル e
WHERE 商店ID = '商店１'
AND NOT EXISTS (SELECT * FROM 対象テーブル f WHERE e.商店ID = f.商店ID AND e.商品ID = f.商品ID AND e.履歴 < f.履歴)
) b
USING (商品ID)
ORDER BY a.価格 - b.価格 DESC;
----------------------------------------

http://gihyo.jp/dev/serial/01/sql_academy2/000901
http://gihyo.jp/dev/serial/01/sql_academy2/000902
http://codezine.jp/article/detail/907

お礼

素晴らしいです。
助かりました。これで出力できました。

こういう風にかけば良いんですね。あまりわかってないので
応用が聞きそうにありませんが、理解していきます。
ありがとうございました。

回答No.2

Bnbnbnta101

create temporary table temp
select
t1.商品ID
,t1.商店ID
,t1.価格
from テーブル1 as t1
inner join
(
select
商品ID,商店ID,max(履歴) as 履歴
from テーブル1
group by 商品ID,商店ID
) as t2 on t1.商品ID = t2.商品ID and t1.商店ID = t2.商店ID and t1.履歴=t2.履歴;
ここでセミコロンですね。

お礼

ありがとうございます。
やはり、ここでセミコロンですね。

ただ、後のSELECT文でtempをテーブルではopenできないというエラーが発生します。。。

他に方法があれば教えて頂ければ、お願いします。

補足

tempで開けない理由がわかりました。

------------
create temporary table temp
select
t1.商品ID
,t1.商店ID
,t1.価格
from テーブル1 as t1
inner join
(
select
商品ID,商店ID,max(履歴) as 履歴
from テーブル1
group by 商品ID,商店ID
) as t2 on t1.商品ID = t2.商品ID and t1.商店ID = t2.商店ID and t1.履歴=t2.履歴;

SELECT * FROM temp;
--------------------

は実行できました。JOINの中でtempを使うとcann't openとなるようです。
何かわかれば教えてもらえれると助かります。

よろしくお願いします。

回答No.1

Bnbnbnta101

一回履歴で絞り込んだ結果をテンポラリテーブルに入れるのが良いと思います。

create temporary table temp
select
t1.商品ID
,t1.商店ID
,t1.価格
from テーブル1 as t1
inner join
(
select
商品ID,商店ID,max(履歴) as 履歴
from テーブル1
group by 商品ID,商店ID
) as t2 on t1.商品ID = t2.商品ID and t1.商店ID = t2.商店ID and t1.履歴=t2.履歴

select
t2.価格-t1.価格 as 価格差
,t1.商品名
,t1.商店
from temp as t1
left join
(
select 価格,商品 from temp where 商店=商店1
) as t2 on t1.商品 = t2.商品
where t1.商店<>商店1
ORDER BY t2.価格 - t1.価格 DESC;

t1テーブルは基準となるテーブルです。
そこに価格,商品毎にmaxの履歴を絞り込んだt2テーブルを内部結合することによって古い履歴は排除されます。
その結果をテンポラリテーブルに突っ込みます。
※すみません、mysqlでテンポラリテーブル使ったことないのでこれでいけるでしょうといったところですが。

あとは前回回答したとおりのことをテンポラリテーブルからおこなっているだけです。
やってることは商店1で絞り込んだ結果をサブクエリで結合しているだけです。
説明下手で申し訳ないですがわかりますでしょうか。

参考URL：

http://logic.moo.jp/data/archives/11.html

お礼

ありがとうございます。鋭意努力中です。
こんなことができるんですね。勉強させて頂きます。

ちなみに、create temporary table tempではエラーが出ないのですが、
このSQL文は；（セミコロン）は最後にのみ付与すれば良いのでしょうか？
構文を見直していますが、

select
t2.価格-t1.価格 as 価格差
,t1.商品名
,t1.商店

phpMyadminで実行してみていますが、上記の文のところでエラーが発生します。

よろしくお願いします。