【SQL】JOINとかよくわかんねぇから物理攻撃で解決してやる

SQLを叩いている際に、JOINなどを使い一発で必要なデータが出力できたときは嬉しいですよね。

ただ、JOINをいっぱいしているとなんの処理してるかこんがらがったり、そもそもJOINでは解決しずらいような問題も多々出てきます。

頑張って試行錯誤してJOINしても良いのですが、ソースコード上のSQLなどではなくて、一時的にデータを出力したいだけな場合には、処理を切り分けて物理攻撃(一つの情報を出すSQLを連打)を仕掛けるのもありだと思います。

いや、俺はSQLから逃げないんだ!!って方は、頑張ってください。
私応援しております。

作業内容例
早速書いてみた
物理攻撃SQLの基本形

作業内容例

今回はあるお店のお客様のリストを出力する例を上げてみました。
できそうな方はぜひSQLオンリーでやってぜひとも僕にやり方を教えて下さい。

抽出条件

・特定の店舗にいるお客様のリストを出したい
・お客様毎に、一番最後に注文した商品の情報も付随して知りたい

想定テーブル

shopsテーブル (店舗情報)

id	店舗ID
name	店舗名
place	住所

usersテーブル(お客様)

id	お客様ID
name	名前
age	年齢
memo	備考

menusテーブル (メニュー詳細)

id	メニューID
name	商品名
price	金額

requestsテーブル (商品注文履歴)

id	注文番号ID
menue_id	メニューID

※updated,createdなどは省略

早速書いてみた

とりあえず見切り発車でSQLを書き始めたところで、

# 店舗IDが1000番のお客様情報を取得する...？？
select u.*,max(r.id) from users u left join requests r on u.id = r.user_id where u.shop_id = 1000 group by u.id;

1 2	# 店舗IDが1000番のお客様情報を取得する...？？ select u.*,max(r.id) from users u left join requests r on u.id = r.user_id where u.shop_id = 1000 group by u.id;

こんな感じのSQLまで私は書いてみました。

ただ、ここから

「どうやったら一番最後に注文された商品の情報を出力するのか?」

がわかリませんでした。

join,,,group by,,,うーん,,,わからん!!

ということで、物理攻撃に走りましょう。

#　STEP1 お客様情報と最後の注文IDを出力する
$ echo "select u.*,max(r.id) from users u left join requests r on u.id = r.user_id where u.shop_id = 1000 group by u.id" | mysql -u root -N | tee user_menue_ids.tsv

# cat user_menue_ids.tsv
...ユーザの情報と注文IDがズラーっと

# STEP2 while文を使って、id毎にひたすらSQLをぶっ叩いていく
$ while read uid name age memo date rid rmenue_id; do echo "select * from menues where id = "$rmenue_id done; &lt; user_menue_ids.tsv | mysql -u root -N | tee result.tsv

#　STEP1 お客様情報と最後の注文IDを出力する

$ echo "select u.*,max(r.id) from users u left join requests r on u.id = r.user_id where u.shop_id = 1000 group by u.id" | mysql -u root -N | tee user_menue_ids.tsv

# cat user_menue_ids.tsv

...ユーザの情報と注文IDがズラーっと

# STEP2 while文を使って、id毎にひたすらSQLをぶっ叩いていく

$ while read uid name age memo date rid rmenue_id; do echo "select * from menues where id = "$rmenue_id done; < user_menue_ids.tsv | mysql -u root -N | tee result.tsv

上記をやってあげると、「お客様の情報+商品ID」「商品ID+商品情報」のリストが作成されます。

後はコマンドでいい感じにくっつけて上げてもいいし、エクセルとかGoogleSpreadSheetに貼り付けてセルフジョインしてしまえば完成ですね。

物理攻撃SQLの基本形

今回物理攻撃SQLというタイトルをつけましたが、結局やっていることはbashのwhileを回して処理をしているだけです。
だた、これを使えるか否かは作業速度に雲泥の差が出るので、SQLに使わなくても覚えるだけ覚えて置くと役に立つかもしれません。

$ while read &lt;カラム...&gt;; do echo "&lt;実行SQL文&gt;"&lt;必要な変数を適宜&gt; done; &lt; &lt;情報が入っているファイル&gt; | mysql -u root -N 【Nオプションはカラムの表示なし】 | tee &lt;結果の出力先&gt;

1	$ while read <カラム...>; do echo "<実行SQL文>"<必要な変数を適宜> done; < <情報が入っているファイル> \| mysql -u root -N 【Nオプションはカラムの表示なし】 \| tee <結果の出力先>