人力検索はてな
モバイル版を表示しています。PC版はこちら
i-mobile

SQL文(MySQL)の質問です。
以下のようなテーブルがあります。

テーブル名:gamePlayData
項目1:playerName (String)
項目2:score (int)

scoreでorder byしたときに、ある特定の条件(playerName='HOGE' and score = 1234)が上から何レコード目にあるかをSQL文だけで取得することができたらそのSQL文を教えてください。
(アクセスが多く負荷をかけたくないので、PHPなどのプログラムで1件目からループさせて条件を判別していく方法は避けたいのでSQL一発で取得したいのです)

※過去の回答履歴を見て、適当な回答を繰り返していると判断した場合、その方の回答は開きません。
支払い率が100%でないのは、最近そういうモラルが感じられない回答がある為です、ご了承ください。

●質問者: irhnhhtn
●カテゴリ:インターネット ウェブ制作
✍キーワード:hoge MySQL PHP Score SQL
○ 状態 :終了
└ 回答数 : 8/8件

▽最新の回答へ

1 ● goodvn
●0ポイント

元のデータを変更しても良い,という前提であれば,インクリメントするカラムを追加して,その値を取れば ok です

つまり,カラムに,"line"(int型) を追加し,SELECT してやります

AUTO_INCREMENT を有効にしておきます.INSERT する際には,このカラムは指定しなくても,自動でインクリメントされた数値が入ります

他にも方法はあると思いますが,PHP で処理するのに比べてコストが安いとは思えません

◎質問者からの返答

AUTO_INCREMENTになっている項目lineがあったとして・・・・

質問のような「特定の条件のレコードは上から何番目か?」

というSQL文は具体的にはどのように書くのでしょうか?


2 ● zero-uhura
●36ポイント

質問意図から少しずれますので、ポイントは結構です。

ある特定の条件(playerName='HOGE' and score = 1234)よりスコアが多い人を数えれば何レコード目にあるか分かると思います。


スコアが多い方が上位の場合です。

また、スコアで比べるので「playerName」は無視します。


SELECT COUNT(*) +1 FROM table WHERE score > 1234;

例)以下の場合

1234ポイントより高いポイントの方は4名で、五番目以降に1234ポイントの方が出てきます、すなわち「HOGE」さんを含む1234ポイントの方は5位タイになります。(同順位なので、5?7の方はすべて5位)


1. 5000 AAA
2. 4000 BBB
3. 3000 CCC
4. 2000 DDD

5. 1234 EEE
6. 1234 HOGE
7. 1234 FFF

8. 1000 GGG
◎質問者からの返答

いや、これはなかなかいいアイデアですね。

要は、負荷がかからなければPHPで多少プログラム書いてもいいんです。

(まさしく質問から外れてしまいますが・・・)

という訳でポイントは差し上げます。


3 ● goodvn
●0ポイント
INSERT INTO playGameData (`playerName`, `score`) VALUES ('HOGE', '1234');

でインサートし,

SELECT `line`, `playerName`, `score` FROM playGameData WHERE playerName='HOGE' AND score = 1234

とすると,line が行数になります

もちろん,最初から入れて置かないと意味がないので,後から追加する場合は,適宜 UPDATE しておく必要があります

◎質問者からの返答

scoreは小さい順に並んでInsertされていけばlineは上から何番目のレコード、を意味することになりますが、scoreの値は大小バラバラでInsertされてくるので、Select時には order by score します。そうすると、lineの値は何番目のレコードという意味をなしません。


4 ● chuken_kenkou
●15ポイント

MySQLの質問をする場合は、バージョンを明記してください。

SQL周辺だけでも、使える機能が大きく違ってきます。

負荷がかからなければPHPで多少プログラム書いてもいい


例えば、行数をカウントするにしても、ストアドプロシジャなどを使えば、MySQLサーバ側で処理が行え、オーバヘッドを大幅に減らせます。

zero-uhuraさんのcount関数を使う方法は、php側の処理は簡単になります。しかし、母体データ件数が多いなら、MySQLサーバ側の負荷を軽減するためにはscore列にインデクスを定義しておくといった措置が必要です。

◎質問者からの返答

MySQLのバージョンは5.0です。phpmyAdminを使ってます。

score列のインデックス定義も了解です。

行数をカウントするストアドプロシジャについて、もしよければ具体的に回答ください。

質問とは外れますが、負荷に対して効果的な内容であればポイントは差し上げます。


5 ● ku__ra__ge
●40ポイント ベストアンサー

以下のSQLで指定したレコードが上から何レコード目にあるか求まります。

サブクエリで各レコードに連番を付与して、指定したレコードのみ抽出しています。

(指定したレコードが複数合った場合は最小値)

SELECT MIN(rank)
 FROM (
 SELECT playerName
 , score
 , @a:=@a+1 AS rank
 FROM gamePlayData
 ,(select @a:=0) AS rank_dummy
 ORDER BY score DESC
 ) AS rank_data
WHERE playerName = 'HOGE'
 AND score = 1234;

ただ、勝手に想像して申し訳ないのですが id:irhnhhtnさん が求めているSQLは、

指定したスコアが何位なのかを求めるSQLのような気がします。

ちなみにそうだとすれば、以下のようなSQLになります。

SELECT MIN(rank)
 FROM (
 SELECT playerName
 , score
 , @a:=@a+1 AS rank
 FROM gamePlayData
 ,(select @a:=0) AS rank_dummy
 ORDER BY score DESC
 ) AS rank_data
WHERE score = 1234;
◎質問者からの返答

なるほど?サブクエリと@変数の使い方は知りませんでした。

ズバリ模範回答でしょう。

ちなみに負荷的にはzero-uhuraさんの方法とどちらがいいんでしょう?

分かれば追加で回答お願いします。


1-5件表示/8件
4.前の5件|次5件6.
関連質問


●質問をもっと探す●



0.人力検索はてなトップ
8.このページを友達に紹介
9.このページの先頭へ
対応機種一覧
お問い合わせ
ヘルプ/お知らせ
ログイン
無料ユーザー登録
はてなトップ