mySQLでグループ別の順位を入力

質問

東京都の学校別のランクを作っています。

現在、name school pointまでは記載されています。
この後、SQL構文で、rank欄に学校内順位を入れ、city欄にtokyoを入れたいと考えています。

完成形は下記のようなものです。
（同点2位が2人いたら、いずれも2とし、その次の人は4となる)

table
name__school__city___point___rank
kameda__1高__tokyo___345____ 1
suzuki__2高__tokyo___341____ 1
kaneko__3高__tokyo___332____ 1
yosida__2高__tokyo___321____ 2
tanita__1高__tokyo___310____ 2
suyama__1高__tokyo___310____ 2
kisida__2高__tokyo___301____ 3
komine__1高__tokyo___299____ 4
tomita__2高__tokyo___289____ 4
sugita__1高__tokyo___275____ 5

下記のreplace文を考えたのですが、学校別の順位ではなく全体順位の入力となってしまいます。

REPLACE INTO table(rank,city)
SELECT (
SELECT COUNT(t2.poit)
FROM table t2
WHERE t2.poitn > t1.point
AND t2.school = t1.school
GROUP BY t2.school
) + 1 AS rank,tokyo
FROM table t1;

学校別の順位を入力するためにはどのように改良すればいいでしょうか?

SQLはmySQL5.5 CentOS6.3です。
よろしくお願いいたします。

回答No.1

yambejp · ベストアンサー

元表のプライマリキーはどうなっているのでしょうか?
nameをidにするとユニークを確保するのがかなり大変ですが大丈夫ですか？

//準備
create table seiseki(name varchar(20) primary key,school varchar(10),city varchar(10) null,point int not null,rank int null);
insert into seiseki(name,school,point) values ('kameda','1高',345),('suzuki','2高',341),('kaneko','3高',332),('yosida','2高',321),('tanita','1高',310),('suyama','1高',310),('kisida','2高',301),('komine','1高',299),('tomita','2高',289),('sugita','1高',275);
select * from seiseki;

//nameごとのschool単位での順位
select name,(select count(*) +1 from seiseki as t2 where 1 and t2.point > t1.point and t2.school= t1.school ) as rank
from seiseki as t1;

//上を応用して、rankとcityを更新
update seiseki as t0
inner join (select name,(select count(*) +1 from seiseki as t2 where 1 and t2.point > t1.point and t2.school= t1.school ) as rank
from seiseki AS t1 ) as t3 on t0.name=t3.name
set t0.rank=t3.rank,t0.city='tokyo';

お礼

ご教授いただき有難うございます。
おかげさまで望む形のものを作ることが出来ました
ご指摘いただきましたIDについては、別にユーザーIDを別に作り対応することにしました。
本当に有難うございました。