SQL文にて順位表をつくりたい

質問

create table team(team_id int not null primary key,team_name varchar(30),year int(4), leagueid int(32) );
insert into team values(1,"A",2012,1),(2,"B",2012,1),(3,"C",2012,1),(4,"D",2012,1),(5,"A",2012,2),(6,"B",2012,2),(7,"C",2012,2),(8,"D",2012,2),(9,"A",2011,1),(10,"B",2011,1),(11,"C",2011,1),(12,"D",2011,1);
create table taisen(id int not null primary key auto_increment,hometeam int,homepoint int,awayteam int,awaypoint int);
insert into taisen (hometeam,homepoint,awayteam,awaypoint)
values(1,3,2,2),(3,1,4,0),(1,0,3,2),(2,2,4,2),(1,1,4,2),(2,0,3,0),(2,1,1,2),(4,1,3,3),(3,5,1,2),(5,3,6,3),(5,1,7,1),(7,1,8,1),(9,1,10,1),(11,1,12,3);

データベース内にデータがあったときに年やleague_idごとに順位表を作りたいのですが、どのようにSQL文を書いてよいかわからず困っています。

理想はこのような感じです。
http://soccer.yahoo.co.jp/ws/standing/?l=52

お分かりの方がいらっしゃいましたらご教授お願いいたします。

回答No.2

yambejp · ベストアンサー

>まだ試合をおこなっていないチームもランク付けしたいのですが、

ではまず、teamテーブルの、2012年1リーグEチームを登録します
insert into team values(13,'E',2012,1);

//v1を作り直します。（すでにある場合は削除してください）

create view v1 as
select id,hometeam as team_id
,case when homepoint>awaypoint then 3 when homepoint = awaypoint then 1 else 0 end as wp
,if(homepoint>awaypoint,1,0) as w
,if(homepoint=awaypoint,1,0) as d
,if(homepoint<awaypoint,1,0) as l
,homepoint as tokuten
,awaypoint as sitten
,team_name,year ,leagueid
from taisen
inner join team on team.team_id=hometeam
union all
select id,awayteam as team_id
,case when homepoint<awaypoint then 3 when homepoint = awaypoint then 1 else 0 end
,if(homepoint<awaypoint,1,0)
,if(homepoint=awaypoint,1,0)
,if(homepoint>awaypoint,1,0)
,awaypoint
,homepoint
,team_name,year ,leagueid
from taisen
inner join team on team.team_id=awayteam
union all
select null,team_id,0,0,0,0,0,0,team_name,year,leagueid
from team
left join taisen on taisen.hometeam=team.team_id or taisen.awayteam=team.team_id
where taisen.id is null

//v2を少しいじります。（すでにあるものは削除）
create view v2 as
select team_id,year,leagueid,team_name
,sum(wp) as wp
,count(id) as count
,sum(w) as w
,sum(d) as d
,sum(l) as l
,sum(tokuten) as tokuten
,sum(sitten) as sitten
,sum(tokuten) - sum(sitten) as tokusitten
from v1
group by team_id,year,leagueid;

//この状態で、前回のSQLを走らせます
select year,leagueid,
(select count(*)+1 from v2 as v2b
where 1
and v2a.year=v2b.year
and v2a.leagueid=v2b.leagueid
and (0
or v2a.wp<v2b.wp
or v2a.wp=v2b.wp and v2a.w<v2b.w
or v2a.wp=v2b.wp and v2a.w=v2b.w and v2a.tokuten<v2b.tokuten
or v2a.wp=v2b.wp and v2a.w=v2b.w and v2a.tokuten=v2b.tokuten and v2a.tokusitten<v2b.tokusitten
)
) as rank,team_id,team_name
,wp,count,w,d,l,tokuten,sitten,tokusitten
from v2 as v2a
order by year,leagueid,rank;

※2012年1リーグの5位にチームEが表示されます

お礼

yambejp 様

度重なる質問に対し、ご回答有難う御座います。
できました！！
本当に有難う御座います。
本当に助かりました。

次は総当り表を実装予定ですが、またわからないことがありましたらご教授お願いできればと思います。

本当に有難うございました。

回答No.1

yambejp

あら、まだこの課題でつまってましたか・・・

面倒なのでviewを使って処理してみました。
もとのデータの持ち方が非効率なのでこのくらいが限界かなと。

//ホームチーム、アウェイチーム関係なくとりあえずビューにまとめる
create view v1 as
select id,hometeam as team_id
,case when homepoint>awaypoint then 3 when homepoint = awaypoint then 1 else 0 end as wp
,if(homepoint>awaypoint,1,0) as w
,if(homepoint=awaypoint,1,0) as d
,if(homepoint<awaypoint,1,0) as l
,homepoint as tokuten
,awaypoint as sitten
,team_name,year ,leagueid
from taisen
inner join team on team.team_id=hometeam
union all
select id,awayteam as team_id
,case when homepoint<awaypoint then 3 when homepoint = awaypoint then 1 else 0 end
,if(homepoint<awaypoint,1,0)
,if(homepoint=awaypoint,1,0)
,if(homepoint>awaypoint,1,0)
,awaypoint
,homepoint
,team_name,year ,leagueid
from taisen
inner join team on team.team_id=awayteam;

//ビュー1から集計したビュー2を作成
create view v2 as
select team_id,year,leagueid,team_name
,sum(wp) as wp
,count(*) as count
,sum(w) as w
,sum(d) as d
,sum(l) as l
,sum(tokuten) as tokuten
,sum(sitten) as sitten
,sum(tokuten) - sum(sitten) as tokusitten
from v1
group by team_id,year,leagueid;

※ここまでビューは一回作ってしまえば2回目からは作成する必要はありません

//順位つき勝敗表
select year,leagueid,
(select count(*)+1 from v2 as v2b
where 1
and v2a.year=v2b.year
and v2a.leagueid=v2b.leagueid
and (0
or v2a.wp<v2b.wp
or v2a.wp=v2b.wp and v2a.w<v2b.w
or v2a.wp=v2b.wp and v2a.w=v2b.w and v2a.tokuten<v2b.tokuten
or v2a.wp=v2b.wp and v2a.w=v2b.w and v2a.tokuten=v2b.tokuten and v2a.tokusitten<v2b.tokusitten
)
) as rank,team_id,team_name
,wp,count,w,d,l,tokuten,sitten,tokusitten
from v2 as v2a
order by year,leagueid,rank;

※ランクの根拠は「勝ち点順>勝ち数順>得点順>得失点順」にしてあります。
当該個所を修正すればランクの調整ができると思います。

※今回の例だと2011年のリーグ2はID=9のAチームとID=10のBチームは
勝ち点、勝ち数、得点、得失点ともにおなじなので同じ順位になります。

お礼

yambejp 様

有難う御座います。

まだ解決しておらず、放置状態のままでした・・
とても助かりました。
本当に有難う御座います。

再度ご質問なのですが、
上記の場合、試合をおこなったチームの場合対象になっておりますが、
まだ試合をおこなっていないチームもランク付けしたいのですが、
どのようにしたらよろしいでしょうか？

度重なる質問で大変申し訳御座いませんが、宜しくお願いいたします。