大変申し訳ありません。本日も質問させてもらったのですが、一部情報(履歴)が不足していて追加で質問させてください。
以下のようなテーブルがあり、価格が異なるところを差額一覧を出したいと思っております。
(先ほどは2名の方に回答頂き、すみませんベスト10の設定がわからず設定できなくて、すみませんでした。)
【テーブル構成】
商品ID、商店ID、価格(円)、履歴
というテーブルがあり、
商品A、商店1、1000、1
商品A、商店2、1500、1
商品A、商店3、800、1
商品A、商店1、2000、2
商品A、商店2、2500、5
商品A、商店3、1800、3
商品B、商店1、2000、1
商品B、商店2、1700、1
商品B、商店3、2600、1
商品B、商店1、2500、4
商品B、商店2、2000、6
商品B、商店3、2800、7
・・・(多くの商品データがあります)
というデータが格納されております。
商品と商店の区分ごとに履歴の最も大きいものを取り出し (←追加)
商店1を基準に、商品毎に商店2、3の価格差を大きい順にソートして出したいと考えています。
SQL分ではどのように記載すれば良いでしょうか?
(実際に比較したデータは以下を抽出し)【履歴の古いのは無視】
商品A、商店1、2000、2
商品A、商店2、2500、5
商品A、商店3、1800、3
商品B、商店1、2500、4
商品B、商店2、2000、6
商品B、商店3、2800、7
・・・(多くの商品データがあります)
(今回の場合の希望出力が以下のような感じです。)
価格差、商品名、商店 (←綱目です。)
800、商品B、商店3
500、商品A、商店2
-200、商品A、商店3
-500、商品B、商店2
ご教授頂けると幸いです。
ちなみに、教えて頂いたSQLをもとに以下のようにやってみましたが、うまくいきませんでした。。。
全くよくわかっていません。少し解説を入れて頂けると幸いです。
------------------------------------
SELECT
a.価格 - b.価格 AS 価格差,
a.商品ID AS 商品名,
a.商店ID AS 商店
FROM 対象テーブル a
INNER JOIN 対象テーブル b ON (a.商品ID = b.商品ID AND b.商店ID = '商店1')
WHERE a.商店ID <> '商店1'
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ベストアンサー
- 回答日時:
商品B-商店3の価格差は 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 = '商店1')
WHERE a.商店ID <> '商店1'
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 = '商店1')
WHERE a.商店ID <> '商店1'
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 <> '商店1'
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 = '商店1'
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
- 回答日時:
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.履歴;
ここでセミコロンですね。
この回答への補足
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となるようです。
何かわかれば教えてもらえれると助かります。
よろしくお願いします。
ありがとうございます。
やはり、ここでセミコロンですね。
ただ、後のSELECT文でtempをテーブルではopenできないというエラーが発生します。。。
他に方法があれば教えて頂ければ、お願いします。
No.1
- 回答日時:
一回履歴で絞り込んだ結果をテンポラリテーブルに入れるのが良いと思います。
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で実行してみていますが、上記の文のところでエラーが発生します。
よろしくお願いします。
お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!
似たような質問が見つかりました
- ネットスーパー 他人の閲覧履歴消せますか? 2 2022/09/19 04:26
- お菓子・スイーツ お菓子についての質問第2弾です!選択肢増やしてみました。 ①性別・年代 ②知らない店でお菓子を買う・ 1 2023/05/04 09:32
- 経営情報システム accessでの請求管理について 12 2022/06/11 16:20
- PostgreSQL PostgreSQL レコードからアイテム種類数を取得したい 2 2022/11/23 22:31
- その他(買い物・ショッピング) 商品に印字してあるバーコードについて 4 2022/09/25 14:12
- その他(ネットショッピング・通販・ECサイト) アマゾンや、楽天の商品評価以外で、外部のサイトで商品の評価をしている大手、人気サイトはありますか。 2 2023/02/02 11:26
- お菓子・スイーツ お菓子について聞いてみたいです(ˊ˘ˋ*) ①性別・年代 ②知らない店でお菓子を買う・食べるきっかけ 1 2023/05/02 22:25
- Excel(エクセル) エクセルデーターの並び替え 5 2022/08/06 09:59
- 大学・短大 至急お願いします 2 2022/04/26 13:59
- 飲食店・レストラン かつやさん値上げしましたか?今日、ロースカツとアジフライの単品買ったんですがあれと?(´・ω・`) 1 2023/03/11 21:05
このQ&Aを見た人はこんなQ&Aも見ています
-
プロが教えるわが家の防犯対策術!
ホームセキュリティのプロが、家庭の防犯対策を真剣に考える 2組のご夫婦へ実際の防犯対策術をご紹介!どうすれば家と家族を守れるのかを教えます!
-
2つの列が同じ値の行を取得するSQL
Oracle
-
SQLで違うテーブルの値を比較して値に差があるレコードを抽出したいので
Oracle
-
同テーブルの差分レコードの抽出
その他(データベース)
-
-
4
GROUP BYを行った後に結合したい。
Oracle
-
5
【SQL】他テーブルに含まれる値に合致する行を抽出
その他(データベース)
-
6
日付型のフィールドに空白を入れる方法を教えてください
その他(データベース)
-
7
count(1)とcount(*)の違い
Oracle
-
8
SQL文で素早くNULLを除外する方法
SQL Server
-
9
LIKEの右側にカラムを指定できますか?
MySQL
-
10
SQLで、Join句で結合したテーブルにデータが無い場合について
SQL Server
-
11
2つのテーブルから条件に一致しないデータ抽出
SQL Server
-
12
select句副問い合わせ 値の個数が多すぎます
Oracle
-
13
sqlのwhereで指定した条件の前後を取得したい
Oracle
-
14
カーソル0件の時にエラーを発生させる
Oracle
-
15
エクセルでアルファベットか数値の判定をしたいのですが
Excel(エクセル)
-
16
<SQL>重複しているデータの場合は最新の日時のものを取得したい
SQL Server
関連するカテゴリからQ&Aを探す
おすすめ情報
このQ&Aを見た人がよく見るQ&A
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
【SQL】他テーブルに含まれる値...
-
結合したテーブルをSUMしたい
-
SQLで、Join句で結合したテ...
-
DB、キャストとインサートを...
-
既存データをINSERT文にして出...
-
Access 別のDBに対してDlookup...
-
同一テーブル内での比較(最新...
-
データ無し時は空白行にしたい...
-
異なるテーブルの値を比較テー...
-
テーブル名が可変の場合のクエ...
-
SQLサーバに対するSQL文で抽出...
-
ExcelのMatch関数のようなもの...
-
Accessの構成をコピーしたい
-
在庫管理のデータベース
-
AccessとSQL Serverの連携について
-
三科目合計点のクラス別平均点...
-
ACCESS クエリーでソートの不具合
-
2つのテーブルをLIKE演算子のよ...
-
異なるデータベース間でinner j...
-
ExcelのVLOOKUP関数の動作をMyS...
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
【SQL】他テーブルに含まれる値...
-
テーブル名が可変の場合のクエ...
-
結合したテーブルをSUMしたい
-
既存データをINSERT文にして出...
-
SQLで、Join句で結合したテ...
-
データ無し時は空白行にしたい...
-
Accessの構成をコピーしたい
-
ACCESSのVBAにてExcelに行...
-
2つのテーブルをLIKE演算子のよ...
-
SQLサーバに対するSQL文で抽出...
-
ExcelのVLOOKUP関数の動作をMyS...
-
PRIMARY KEYのコピー
-
同一テーブル内での比較(最新...
-
ExcelのMatch関数のようなもの...
-
ADO+ODBCでテーブルに接続する...
-
Access 別のDBに対してDlookup...
-
改行を含んだデータのインポート
-
ACCESS クエリーでソートの不具合
-
複数のテーブルからデータを取...
-
副問合せを使わずにUNIONと同様...
おすすめ情報