Mysqlのテーブル作成

Question

初心者です。
mysqlで、テーブルを作成しています。
定価から仕入れ価格を引いた差額を出すために、以下のコードを作成しました。
条件としては、
差額が最大であるデータを抽出します。

select
    i.name 商品名称
    ,i.list_price 定価
    ,pur.price 仕入価格
    ,i.list_price-pur.price 差額
from
    purchases pur
left join
    items i
    on pur.item_id=i.id
where
	i.list_price-pur.price=
	(select
		max(i.list_price-pur.price)
    from
    purchases pur
    left join
    items i
    on pur.item_id=i.id);

御覧の通り、
同じleft joinを二度利用しています。
こういった二度手間がないように、すっきりとさせたいのですが、どこを変更したらよいかわかりません。
教えていただきたいです。よろしくお願いします。

Comments

[K_3578]

left joinを複数回やるのがめんどいって話っすよね？

[xxx0_0xxx]

より効率的なコードの書き方を求めています。
見栄えも悪いので何かいい方法はないかと探しています。

[K_3578]

テーブル定義提示してくれると助かります。
とは言え、コード丸々差し上げるとただの作業になるんで、私は回答するにしても
アドバイス程度に留めますが

[m.ts10806]

問題は「作成（タイトル）」ではないと思うのですが、如何でしょうか。

[m.ts10806]

いずれにしてもSELECTだけ提示されても何とも言えません。
必要なデータと現在のテーブル定義（複数テーブルがあるなら関連性が分かるER図もあればなおよし）を提示してください。

[hoshi-takanori]

特定のクエリーを改善したいというより、汎用的に使える手法を知りたいってことですよね。

MySQL 8 以降なら WITH 句を使えば良いのでは。
https://tech.bita.jp/article/33

こっちの方が分かりやすいかも。
https://qiita.com/mzuk/items/78a0c94829832e0c4c7c

Answer 1

今回のようなケースであればRANK()関数を使うことでサブクエリを減らすことができます。

SELECT *
FROM (
  SELECT
    I.name AS 商品名称,
    I.list_price AS 定価,
    P.price AS 仕入価格,
    I.list_price - P.price AS 差額,
    RANK() OVER (ORDER BY I.list_price - P.price DESC) AS rnk
  FROM purchases P
  JOIN items I ON P.item_id = I.id
) T
WHERE rnk = 1

Comments

[xxx0_0xxx]

実際にコード書いて実行してみた結果、すっきりした形になりました！！
rank関数を使う発想が全くなかったので驚きです！
大変勉強になりました。
本当にありがとうございました（^^)