みんなの「教えて(疑問・質問)」にみんなで「答える」Q&Aコミュニティ

こんにちはゲストさん。会員登録(無料)して質問・回答してみよう!

締切り済みの質問

MySQLでの順位付けについて

はじめて質問させていただきます。MySQLについての質問なのですが、

以下のようなテーブル(goods)があります

テーブル名 goods
商品カテゴリ category
商品名 name
商品価格 price

テーブル名 goods
----------------
(category)(name) (price)
fashion  バッグ  15000      
food   ラーメン 3000      
fashion  ポーチ  5000      
  fashion  腕時計  16000    
  food   メロン  2000  
----------------
       ↓
-----------------
(rank)(category)(name) (price)
1 fashion  腕時計  16000      
2 fashion  バッグ  15000      
  3 fashion  ポーチ  5000    
-----------------

上のテーブルで商品カテゴリ(category)の中の(fashion)のみを抽出して、そして商品価格(price)に関して降順に並べ替え、更に順位をつけたいのですが、以下のように
自分なりに書いてみましたがエラーが出てしまいます。色々と過去の質問等を参考にしたのですが、何卒お力添えの程よろしくお願い致します。

------------------------------------------------------------------------------------------------
<?
$conn = mysql_pconnect ("localhost", "xxx", "xxx")
or die ('I cannot connect to the database.');
mysql_select_db ("xxx",$conn);

$sql= "select x.category,x.price count(*) as rownum from goods as x,goods as y where x.price < y.price or x.price = y.price and x.category > y.category or x.price = y.price and x.category = y.category group by x.category,x.price having category = 'fashion' order by x.price desc,x.category";
;
$res = mysql_query($sql);

while($row = mysql_fetch_array($res)){
echo "<table cellpadding='0' cellspacing='0'>";
echo "<tr><td>";
echo "$row[rank]"
echo "</td>";
echo "<td>";
echo "$row[name]"
echo "</td>";
echo "<td>";
echo "$row[price]"
echo "</td></tr>";
echo "</table>";
}
?>

---------------------------------------------------------------------------------------------------

while($row = mysql_fetch_array($res)){ ・・・の行にエラーがあるというところまで解ったのですが。

投稿日時 - 2008-08-02 00:38:16

QNo.4221419

すぐに回答ほしいです

このQ&Aは役に立ちましたか?

10人が「このQ&Aが役に立った」と投票しています

回答(3)

ANo.3

すみません、mysqlのバージョンによって変数の評価順位がことなるので
ご指摘のようになるようです。

mysql5だとこんな感じでしょうか・・・
もう少しチューニングできそうですが、ぱっとおもいついたのでスピードは
いまいちかもしれません。

select rank,g.category,g.name,g.price
from goods as g
inner join
(select distinct @p:=price as price ,(select count(*) from goods where category='fashion' and price>@p)+1 as rank from goods) as temp
using(price)
where g.category='fashion'
order by g.price DESC

これならSQLの発行は1行です

投稿日時 - 2008-08-04 09:55:34

ANo.2

個々の状況により若干ちがいがありますが、総じて以下のようになります

$sql="SET @count=0";
$res = mysql_query($sql);
$sql="create temporary table temp select price,@count+1 as RANK ,@count:=@count+count(*) as count from goods where category='fashion' group by price order by price DESC";
$res = mysql_query($sql);
$sql="select rank,g.category,g.name,g.price from goods as g inner join temp using(price) where g.category='fashion' order by g.price DESC";
$res = mysql_query($sql);
while($row = mysql_fetch_array($res)){
・・・
}

なお今回例示したSQLは古いバージョンでも対応できるように
テンポラリを使いましたが、MySQL4.1以降でサブクエリがつかえる環境
であればもう少し効率的な書き方もできると思います。

投稿日時 - 2008-08-03 13:04:54

補足

yambejp様、お世話になります。何度も面倒を見ていただき、恐縮しております。ひとつひとつyambejp様に書いていただいた記述を丸写しするのではなく、意味を確認しながら、以下のように書いてみました。

<?
$conn = mysql_pconnect ("localhost", "xxx", "xxx")
or die ('I cannot connect to the database.');
mysql_select_db ("xxx",$conn);

$sql="SET @count=0";
$res = mysql_query($sql);
$sql="create temporary table temp select price,@count+1 as RANK ,@count:=@count+count(*) as count from goods where category='fashion' group by price order by price DESC";
$res = mysql_query($sql);
$sql="select rank,g.category,g.name,g.price from goods as g inner join temp using(price) where g.category='fashion' order by g.price DESC";
$res = mysql_query($sql);

while($row = mysql_fetch_array($res)){
echo "<table cellpadding='0' cellspacing='0'>";
echo "<tr><td>";
echo "$row[rank]";
echo "</td>";
echo "<td>";
echo "$row[name]";
echo "</td>";
echo "<td>";
echo "$row[price]";
echo "</td></tr>";
echo "</table>";
}
?>

しかしながら、価格の降順にはなるのですが、順位の数字が以下のようにばらばらになってしまい、また順位の数字も(1,2,3)の3つしか表示されません。やはりどこか私の記述に間違いがあるのでしょうか?

-----------------
(rank)(name) (price)
1   腕時計  16000      
3   バッグ  15000      
2   ポーチ  5000    
3     傘  4000    
2   ハンカチ 2000    



-----------------

MySQLのバージョンはMySQL5.0 です。今一度ご面倒頂きます様お願いします。

投稿日時 - 2008-08-03 14:16:19

ANo.1

とりあえずSQLで順位をつけるとこまでやってみました

SET @count=0;
create temporary table temp
select price,@count+1 as RANK ,@count:=@count+count(*) as count
from goods
where category='fashion'
group by price
order by price DESC;
select rank,g.category,g.name,g.price
from goods as g
inner join temp using(price)
where g.category='fashion'
order by g.price DESC

この3つのSQLを実行し、結果をPHPでしょりすればよいでしょう

投稿日時 - 2008-08-02 16:03:26

補足

yambejp様、返事が遅れましてすみません。yambejp様のアドバイスの通り、3つのSQLを実行して試してみたのですが苦戦しております。
まず、最初に、エラーが出てしまうと言う事です。エラー内容は次の通りです。
Parse error: syntax error, unexpected '@' in /・・・
行数で言いますと、「SET @count=0;」が記述してある行です。この場所で引っかかってしまって先に進みません。
あと、
>結果をPHPでしょりすればよいでしょう
とは、echoで表示すれば良いという意味でしょうか?
力不足で的外れな補足内容になってしまっておりましたらすみません。よろしくお願いします。

投稿日時 - 2008-08-02 23:30:14

あなたにオススメの質問