SQL文(MySQL)の質問です。

以下のようなテーブルがあります。

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

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

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

回答の条件
  • 1人2回まで
  • 登録:2009/01/16 17:16:48
  • 終了:2009/01/17 20:26:44

ベストアンサー

id:ku__ra__ge No.5

ku__ra__ge回答回数118ベストアンサー獲得回数402009/01/16 21:56:12

ポイント40pt

以下の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;
id:irhnhhtn

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

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

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

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

2009/01/17 10:12:48

その他の回答(7件)

id:goodvn No.1

goodvn回答回数228ベストアンサー獲得回数182009/01/16 17:42:16

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

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

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

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

id:irhnhhtn

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

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

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

2009/01/16 18:09:34
id:zero-uhura No.2

zero-uhura回答回数14ベストアンサー獲得回数32009/01/16 17:51:06

ポイント36pt

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

ある特定の条件(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
id:irhnhhtn

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

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

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

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

2009/01/16 18:15:11
id:goodvn No.3

goodvn回答回数228ベストアンサー獲得回数182009/01/16 19:21:59

INSERT INTO playGameData (`playerName`, `score`) VALUES ('HOGE', '1234');

でインサートし,

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

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

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

id:irhnhhtn

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

2009/01/16 19:59:05
id:chuken_kenkou No.4

chuken_kenkou回答回数722ベストアンサー獲得回数542009/01/16 20:20:54

ポイント15pt

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

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

  • MySQL 4.0以前・・・一部のjoin、union、一時表などは実装済
  • MySQL 4.1・・・joinの実装範囲の拡張、union、unicodeなどの実装
  • MySQL 5.0・・・ビュー、ストアドプロシジャ、トリガなどの実装

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


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

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

id:irhnhhtn

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

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

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

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

2009/01/17 09:52:00
id:ku__ra__ge No.5

ku__ra__ge回答回数118ベストアンサー獲得回数402009/01/16 21:56:12ここでベストアンサー

ポイント40pt

以下の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;
id:irhnhhtn

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

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

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

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

2009/01/17 10:12:48
id:otasuketyan No.6

otasuketyan回答回数41ベストアンサー獲得回数02009/01/16 22:34:05

もしテーブルのフィールドが

key,playerName,score

(1,Jon,2000)

(2,May,5600)

(3,Him,0022)

...

とあるのであれば、max(key)でフィールド最大数を取得し、count(key)で条件に一致したフィールドの数をカウントして、引き算するという方法があります。

id:irhnhhtn

keyはAUTO_INCREMENTになると思いますが、テストデータなど追加・削除する場合があるので

max(key)=最大件数にはなりません。都度採番し直すのも現実的じゃありません。

引き算する方法については既にzero-uhuraさんから回答頂いております。

2009/01/17 10:28:06
id:komamitsu No.7

komamitsu回答回数21ベストアンサー獲得回数22009/01/17 14:24:00

ポイント34pt

http://www.amazon.co.jp/%E9%81%94%E4%BA%BA%E3%81%AB%E5%AD%A6%E3%...

に載っていた自己非等値結合という手法で。

mysql> SELECT * FROM gamePlayData;
+------------+-------+
| playerName | score |
+------------+-------+
| abc        |    50 |
| def        |    80 |
| ghi        |    20 |
| jkl        |   100 |
| mno        |    10 |
+------------+-------+
5 rows in set (0.00 sec)

という状態で

mysql> SELECT p1.playerName, p1.score,
    ->         (SELECT COUNT(p2.score)
    ->          FROM gamePlayData p2
    ->          WHERE p2.score > p1.score) + 1 AS rank
    -> FROM gamePlayData p1
    -> ORDER BY rank
    -> ;
+------------+-------+------+
| playerName | score | rank |
+------------+-------+------+
| jkl        |   100 |    1 | 
| def        |    80 |    2 | 
| abc        |    50 |    3 | 
| ghi        |    20 |    4 | 
| mno        |    10 |    5 |
+------------+-------+------+
5 rows in set (0.00 sec)

とすると個々のレコードの位置(=自分よりもscoreが大きいレコード数)をrankとして取得できます。

なので、

mysql> SELECT p1.playerName, p1.score,
    ->         (SELECT COUNT(p2.score)
    ->          FROM gamePlayData p2
    ->          WHERE p2.score > p1.score) + 1 AS rank
    -> FROM gamePlayData p1
    -> WHERE p1.playerName = 'def' and p1.score = 80
    -> ;
+------------+-------+------+
| playerName | score | rank |
+------------+-------+------+
| def        |    80 |    2 | 
+------------+-------+------+
1 row in set (0.00 sec)

とすると指定されたレコードの位置が取得できます。

なお、今回はscoreの降順でやってますが

p2.score > p1.score をいじれば昇順にもなります。

id:irhnhhtn

詳しく丁寧な回答ありがとうございます。

今までの回答によるとSELECT文は大きく分けて

・サブクエリで入れ子にして1回で問い合わせする(回答頂いたようなSQL文で)

・全体の件数/自分より大きいscoreの件数を複数回問い合わせしPHP側で結果を元に割り出す

の2通りありますが、やはり前者の方がDBへの負荷は少ないんでしょうか

2009/01/17 15:53:41
id:komamitsu No.8

komamitsu回答回数21ベストアンサー獲得回数22009/01/17 16:55:00

ポイント5pt

改めて問題と回答一覧を見てみましたが、ランキング一覧を作る必要は無く、当該レコードの順位さえわかれば良いのですよね。

なので私のやり方はやりすぎ(不必要なクエリーが発行されている)でした。失礼。


もしscoreだけがキーになるのでしたらzero-uhuraさんの回答が一番簡単で負荷も軽いと思いますよ。コメントで「PHP側で…」とありますが、SQL文一発だと思われ。

> ・サブクエリで入れ子にして1回で問い合わせする(回答頂いたようなSQL文で)

> ・全体の件数/自分より大きいscoreの件数を複数回問い合わせしPHP側で結果を元に割り出す

> の2通りありますが、やはり前者の方がDBへの負荷は少ないんでしょうか


一応、こちらにも。

一般的に、SQL負荷の合計が同程度であれば、SQLのパースやらネットワーク負荷やらがあるので前者のほうが良いでしょう。

id:irhnhhtn

>前者のほうが良いでしょう。

やはりそうですよね。

ありがとうございました。

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

    そんなこと質問文のどこにも書いてないですよ

    自分の質問文が曖昧なのに,それに対する回答に対し,ちょっと態度が失礼かと思います

    この行為ははてなに通報しておきます
  • id:irhnhhtn
    >>goodvnさん
    不適切な回答とさせて頂いたのは、「scoreでorder byしたときに」という質問の意図を無視した内容となっていたためです。
    バラバラに並んでいなければ、order by scoreと記述する必要はない訳です。

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

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

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

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