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


例えば表1のデータがあった場合は、
『表1』
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

『表2』のようにしたいです。価格が同じであれば、SHOUHIN_KBNが小さい順、かつ商品区分が同じ場合は、同ランキングにならないよう、HEX(SID)などしてどちらか一方を拾いたい
『表2』
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

表1は、以下の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回まで
  • 13歳以上
  • 登録:2013/11/12 03:07:48
  • 終了:2013/11/13 01:32:33

ベストアンサー

id:windofjuly No.1

うぃんど回答回数2625ベストアンサー獲得回数11492013/11/12 16:57:09

ポイント200pt

(ステップ1)思い通りの順番に並べるクエリ

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

(ステップ2)ユーザー変数を付加してランク付け
SQLその1

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

SQLその2

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
;

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

(ステップ3)一位だけ抜き出し
SQLその1

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

SQLその2

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、その2の2つのクエリを連続して実行させる必要があります。

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

>@変数を使わない

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

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

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

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

    ユーザ変数を使ってSQLだけでランク付けも出来ますけど、
    あとでメンテナンスすることを考えたら、やめといたほうが・・・。
  • id:yayayai
    ありがとうございます!!
    ・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でランク付けする方法を回答しました。

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

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

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

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

絞り込み :
はてなココの「ともだち」を表示します。
回答リクエストを送信したユーザーはいません