MySQLで変数やループ、上位3名を求める方法

質問

お世話になります。早乙女遙佳と申します。

Webサイト上から会員名や点数を入力し、
送信ボタンが押されると、
受け取ったCGI側でDBに書き込み、
今月成績の良かった方、上位3名は○○さんと○○さんと○○さんです
というサイトを作りたいと思っています。

データベースは、MySQLを使って学習していますが、
教えていただけますでしょうか？

scoreテーブルとして、以下のテーブルを作っています。
会員の毎月の点数と、先月からの点数の増加分を格納するテーブルです。
ID name score month increment
1 taro 100 11 0
2 jiro 200 11 0
3 taro 50 10 0
4 jiro 250 10 0

/* 今月の点数を求める */
SELECT score AS thisMonthScore
FROM `score`
WHERE name = 'taro'
AND MONTH = '11';

/* 先月の点数を求める */
SELECT score AS prevMonthScore
FROM `score`
WHERE name = 'taro'
AND MONTH = '10';

/* 点数の増加分を書き込む */
UPDATE `score` SET `increment`= 50 WHERE name = 'taro' and month = '11';

質問１
上記のUPDATE文では、増分を書き込むために、
`increment`= 50
としていますが、
upScore = thisMonthScore - prevMonthScore
`increment`= upScore
のように書けたらと思うのですが、できないものでしょうか？

質問２
WHERE name = 'taro'
の様に各会員の氏名を書いていますが、
「すべての会員についてループさせる」
様にはできないでしょうか？

擬似コードですが
memberName = select name from `score`; //会員名の配列
memberCount = select count('name') as cnt from `score`; //会員数
for (i = 0; i < memberCount; i++){
　thisMonthScore = SELECT score AS thisMonthScore FROM `score` WHERE name = 　memberName[i] AND MONTH = '11'; //今月の点数
　prevMonthScore = SELECT score AS prevMonthScore FROM `score` WHERE name = memberName[i] AND MONTH = '10'; //先月の点数
　upScore = thisMonthScore - prevMonthScore; //点数の増加分
　UPDATE `score` SET `increment`= upScore WHERE name = memberName[i] and month = '11'; //DBの更新
}
のようなイメージです。
DBの呼び出し元であるCGIでループを書けばもちろんできるのですが、
SQL文で処理させた方が速いのかなとも思ったりもしたのですが、
通常、どのように書くものでしょうか？
毎月100件、1年で1000件、10年分で10000件くらいのレコード数になりますが、このくらいであればあまり速度などは気にせずとも良いものでしょうか？

質問３
成績の良かった方、上位3名を抽出するにはどのようなSQL文になるでしょうか？
第一位　taroさん 50点
第二位　jiroさん -50点
第三位　saburoさん -100点
のような感じです。

初歩的な質問で恐縮ですが、
どうぞ、よろしくお願いします。

回答No.2

yambejp · ベストアンサー

仮にこうします
monthは月の整数でもつと、年をまたいだ時に大変なので、日付型のデータの方がよいです

//元データ
create table score (id int not null primary key auto_increment, name varchar(20), score int, month date, increment int not null,unique(name,month));
insert into score(name,score,month) values
('taro',100,'2013-11-01')
,('jiro',200,'2013-11-01')
,('saburo',150,'2013-11-01')
,('hanako',200,'2013-11-01')
,('yosiko',160,'2013-11-01')
,('taro',50,'2013-10-01')
,('jiro',200,'2013-10-01')
,('saburo',250,'2013-10-01')
,('hanako',300,'2013-10-01')
,('yosiko',100,'2013-10-01');

//incrementを求める
update score as s1
inner join score as s2
on s1.month + interval 1 month =s2.month
and s1.name=s2.name
set s2.increment=s2.score-s1.score;
where s2.month='2013-11-01'

//雑なランク付け
select name,increment
from score
where month='2013-11-01'
order by increment desc
limit 3;

ランク付けは同順位の処理をどうするか考えるとすこし複雑になってきますが
今回はとりあえず表示するだけの簡単な文法を提示しておきます

お礼

ご回答、ありがとうございます。

週末、ミックさんのサイト
http://www.geocities.jp/mickindex/database/idx_database.html
を見つけ、学習していました。
手続き型の通常の言語と異なり、
SQLは集合操作を意識した点が特色のようです。

発想の転換が求められる感じで、
いろいろ慣れない点もありますが、理解を深めていきたいと思います。

ご回答、ありがとうございましたｍｍ

回答No.1

原沢 信道（@nharasawa）

質問２
update score s1,score s2 set s1.increment=s1.score-s2.score
where s1.month='11' and s2.month='10' and s2.name=s1.name
設問３
select concat(name,'さん'),concat(increment,'点') from score where month='11'
order by increment desc limit 3;

お礼

ご回答、ありがとうございます。
＃２の方と合わせて、同じ結果を求めるにも違う方法もあるのだと、ためになりました。
また、機会がございましたら、お願いいたします。
ありがとうございましたｍｍ。