id:yayayai
ポイントあり200ptベストアンサーあり
ウェブ制作

mysqlで同一商品コードの中から、最も価格が安い商品を抽出したいです。


例えば表１のデータがあった場合は、
『表１』
SHOUHIN_CD SHOUHIN_KBN SID SCODE RANKING PRICE
A02071409 2 wwww xkjkljlkjas 1 1772
A02071409 2 xxxx bbbbddd 1 1772
A02071409 1 wwww kljkj;lkjada 1 1772

『表２』のようにしたいです。価格が同じであれば、SHOUHIN＿KBNが小さい順、かつ商品区分が同じ場合は、同ランキングにならないよう、HEX(SID)などしてどちらか一方を拾いたい
『表２』
SHOUHIN_CD SHOUHIN_KBN SID SCODE RANKING PRICE
A02071409 2 wwww xkjkljlkjas 2 1772
A02071409 2 xxxx bbbbddd 3 1772
A02071409 1 wwww kljkj;lkjada 1 1772

表１は、以下のSQLで抽出しました
SELECT
rr1.SHOUHIN_CD
,rr1.SHOUHIN_KBN
,rr1.STORE_ID
,rr1.CODE
,(SELECT COUNT(rr2.price) + 1 AS COUNT FROM SHOUHIN rr2
WHERE rr2.SHOUHIN_CD = rr1.SHOUHIN_CD
AND rr2.price < rr1.price
AND rr2.SHOUHIN_KBN < rr1.SHOUHIN_KBN
AND HEX(rr2.STORE_ID) < HEX(rr1.STORE_ID)
) AS RANKING
,rr1.price
FROM
shouhin rr1
ORDER BY rr1.SHOUHIN_CD
,price

SQLを教えてください。どうぞよろしくお願い致します。

回答の条件
  • 1人5回まで
  • 登録：
  • 終了：2013/11/13 01:32:33
※ 有料アンケート・ポイント付き質問機能は2023年2月28日に終了しました。

ベストアンサー

id:windofjuly No.1

回答回数2625ベストアンサー獲得回数1149

ポイント200pt

（ステップ１）思い通りの順番に並べるクエリ

SELECT SHOUHIN_CD, SHOUHIN_KBN, STORE_ID, PRICE
FROM shouhin
ORDER BY SHOUHIN_CD, PRICE, SHOUHIN_KBN, STORE_ID
;

（ステップ２）ユーザー変数を付加してランク付け
SQLその１

SET @cd = '', @rank:=0;

SQLその２

SELECT SHOUHIN_CD, SHOUHIN_KBN, STORE_ID, PRICE
    , @rank := IF( @cd <> SHOUHIN_CD, 1, @rank + 1 ) AS RANK
    , @cd := SHOUHIN_CD
FROM shouhin
ORDER BY SHOUHIN_CD, PRICE, SHOUHIN_KBN, STORE_ID
;

その１、その２の２つのクエリを連続して実行させる必要があります。

（ステップ３）一位だけ抜き出し
SQLその１

SET @cd = '', @rank:=0;

SQLその２

SELECT SHOUHIN_CD, SHOUHIN_KBN, STORE_ID, PRICE, RANK
FROM (
    SELECT SHOUHIN_CD, SHOUHIN_KBN, STORE_ID, PRICE
        , @rank := IF( @cd <> SHOUHIN_CD, 1, @rank + 1 ) AS RANK
        , @cd := SHOUHIN_CD
    FROM shouhin
    ORDER BY SHOUHIN_CD, PRICE, SHOUHIN_KBN, STORE_ID
) r
WHERE RANK = 1
;

その１、その２の２つのクエリを連続して実行させる必要があります。

他1件のコメントを見る
id:windofjuly

＞@変数を使わない

（３）「最も価格が安い商品のみを出したい」だけ考えてみましたが、
最初にコメントしたとおり重くても知りませんよ…。
ランク付けについてはもっともっと複雑になるので、正直考えたくもないです^^;

SELECT a.SHOUHIN_CD, a.PRICE, a.SHOUHIN_KBN, a.STORE_ID
FROM shouhin a
WHERE (a.SHOUHIN_CD, a.PRICE, a.SHOUHIN_KBN, a.STORE_ID) = (
    SELECT b.SHOUHIN_CD, b.PRICE, b.SHOUHIN_KBN, b.STORE_ID
    FROM shouhin b
    WHERE b.SHOUHIN_CD = a.SHOUHIN_CD
    ORDER BY b.SHOUHIN_CD, b.PRICE, b.SHOUHIN_KBN, b.STORE_ID
    LIMIT 1
)
ORDER BY a.SHOUHIN_CD
;
2013/11/12 22:02:47
id:yayayai

ありがとうございました!
そうでしたね。。やはり負荷を考えると前者ですね。
前者の方で実装してみます！

2013/11/13 01:32:11

コメント（3件)

  • id:windofjuly
    うぃんど 2013/11/12 11:02:18
    疑問とりあえず３点
    ・SID って STORE_ID ？
    ・10進数でも16進数でも値の大きさに違いはないのに、HEX で16進数にするのはなぜ？
    ・最終的に出力するときの並び順は無視でいいのかな？

    ところで、
    全体で100レコードだとして、
    商品コード重複が平均10レコードだとしたら、
    サブクエリは100x10で1,000レコード分動くことになって効率悪いですよ。
    全体で1000レコードだとして、
    商品コード重複が平均20レコードだとしたら、
    サブクエリは1000x20で20,000レコード分動くことに･･･。

    phpなどでユーザーインターフェース作ると思いますので、
    ソートされるような並びでインデックスを作っておいて、
    順位はphpなどで後から付加するほうが楽だと思います。
    後付なら自分でもできると思いますよ。

    ユーザ変数を使ってSQLだけでランク付けも出来ますけど、
    あとでメンテナンスすることを考えたら、やめといたほうが･･･。
  • id:yayayai
    FujiiRock 2013/11/12 15:24:44
    ありがとうございます!!
    ・SID って STORE_ID ？
    　⇒はいそうです!
    ・10進数でも16進数でも値の大きさに違いはないのに、HEX で16進数にするのはなぜ？
    　⇒！ご指摘の通りです。勘違いしました。。
    ・最終的に出力するときの並び順は無視でいいのかな？
    　⇒並び順は無視でいいです。

    サブクエリは1000x20で20,000レコード分動くことに･･･。
    　→そうですね。。気になっていましたが。。
    　　順位ずけは、PHP側でやるようにしようと思います。
    　　ただ、順位づけは不要としても、mysqlで同一商品コードの中から、
    　　最も価格が安い商品のみを出したいはやりたいです～
    　　
    　　表2の例でいうと、以下がのこる形です。
    　　A02071409 1 wwww kljkj;lkjada 1 1772
    　　
    　　これが、商品コード単位で出力されているという形です。
  • id:windofjuly
    うぃんど 2013/11/12 17:51:39
    ＞最も価格が安い商品のみを出したい

    SQLでランク付けする手順は必要になるので、
    結局、SQLでランク付けする方法を回答しました。

    簡単な動作テストはしてますが、
    ステップが進むごとに、数行増えていく形にしてありますので、
    順に確認しながらやってみてください。

この質問への反応（ブックマークコメント）

「あの人に答えてほしい」「この質問はあの人が答えられそう」というときに、回答リクエストを送ってみてましょう。

これ以上回答リクエストを送信することはできません。制限について

回答リクエストを送信したユーザーはいません
ユーザー登録をして使いはじめる!無料
すでに登録されている方はこちらからログイン
この質問に含まれるキーワード
知りたいことを検索してみよう
▲ページトップへ