人力検索はてな
モバイル版を表示しています。PC版はこちら
i-mobile

SQL文についてご教授願います。

【tableA:商品一覧テーブ】
id, name
1 , りんご
2 , みかん
3 , いちご


【tableB:売上表テーブル】
id, date, price
1 , 1265375594 ,50
1 , 1260265023 ,57
2 , 1265375594 ,150
2 , 1260265023 ,160


【抽出したいデータ】
id, name, date, price, price2, price3
1, りんご, , , ,
2, みかん, , , ,
3, いちご, , , ,
4, ×××, , , ,
5, ○○○, , , ,
6, △△△, , , ,


date = IDごとの最新日付
price = IDごとの最新日付の値段
price2 = priceより1つ前に売上があった日の値段
price3 = priceとprice2の差額

・各IDごとのデータがほしい
・商品によっては売上の無い日もある
・IDと日付で一意

(参考)
仕様:MYSQL5.1
日付:UNIXTIMEのINT型


●質問者: dekapurio
●カテゴリ:ウェブ制作
✍キーワード:name SQL いちご みかん りんご
○ 状態 :終了
└ 回答数 : 2/2件

▽最新の回答へ

1 ● chuken_kenkou
●150ポイント

price2は、アプリ側で計算すればいいと思いますが、SQLだけで”無理矢理”、やりたいでしょうか?

MySQLでは、@で始まる”ユーザ変数”を使えるので、price2はユーザ変数で求める例です。

select
 A.id
 ,A.name
 ,GB.maxdate
 ,date(from_unixtime(GB.maxdate)) as hizuke
 ,@price:=(select `price` from tableB where id=A.id and `date`=GB.maxdate) as `price`
 ,@price1:=(select `price` from tableB where id=A.id and `date`<GB.maxdate order by `date` desc limit 1) as `price2`
 ,@price-@price1 as price2
 from tableA as A
 left join 
 (select
 id
 ,max(`date`) as maxdate
 from tableB
 group by id) as GB
 on A.id=GB.id
 order by A.id
◎質問者からの返答

chuken_kenkou様、回答有難うございます。

7行目の最後は、as `price1`でよかったですかね?

どちらにしても、動かなかったです。

>SQLだけで無理矢理やりたいでしょうか

厳密にいうと、price2の大きい順に10件だけ表示をしたかったので、素人考えですがprice2も必要なのかなと思っています。


2 ● Km1967
●300ポイント ベストアンサー

まずは下記を実行して趣旨を理解してほしい!

以前kn1967さんが作ってくださった「SQLを実行してHTMLのTABLEとして出力するためのルーチン」を用いてメインルーチン部分にて実行すると楽だと思う!!

SET @id = '', @c = 0;

SELECT a.*
 , FROM_UNIXTIME(b.date)
 , b.price
 , IF(@id <> a.id, @c := 1, @c := @c + 1) c
 , IF(@id <> a.id, @id := a.id, @id := @id) i
 , IF(@c = 1, b.date, 0) date
 , IF(@c = 1, b.price, 0) price
 , IF(@c = 2, b.price, 0) price2
FROM tableA a
LEFT JOIN tableB b ON b.id = a.id
ORDER BY a.id, b.date DESC;

ご理解いただけたら下記に丸ごと置き換える!

price2の上位10件に絞るようにORDER BYとLIMITもつけてあるので、このあたりは適当に変更よろし!!

SET @id = '', @c = 0;

SELECT c.id, c.name
 , FROM_UNIXTIME(SUM(c.date)) date, SUM(c.price) price, SUM(c.price2) price2
 , SUM(c.price2) - SUM(c.price) price3
FROM(
 SELECT a.*
 , IF(@id <> a.id, @c := 1, @c := @c + 1) c
 , IF(@id <> a.id, @id := a.id, @id := @id) i
 , IF(@c = 1, b.date, 0) date
 , IF(@c = 1, b.price, 0) price
 , IF(@c = 2, b.price, 0) price2
 FROM tableA a
 LEFT JOIN tableB b ON b.id = a.id
 ORDER BY a.id, b.date DESC
) c
GROUP BY c.id, c.name
ORDER BY price2 DESC
LIMIT 0, 10;

以上、テスト済みのコピペ!!

◎質問者からの返答

Km1967さん回答ありがとうございます。

結論からいうと、MYSQLではどのSQLもtableAが1000レコード、tableBが200,000レコードというデータ量に対応できていないようです。

(phpmyadminで試していますが、読み込み中のままブラウザが固まる)

データを大幅に減らして(10レコード、100レコード)試してみると、Km1967さんのSQLも、chuken_kenkouさんのSQLでもうまく動作しました。

ここまでSQLでやらせること自体が間違っているのでしょうが・・・

関連質問


●質問をもっと探す●



0.人力検索はてなトップ
8.このページを友達に紹介
9.このページの先頭へ
対応機種一覧
お問い合わせ
ヘルプ/お知らせ
ログイン
無料ユーザー登録
はてなトップ