php mysqlを使用してのリーグ表作成

質問

以前にご質問させていただき、さらに教えていただきたいところがあり、投稿しました。
以前の質問内容は
http://okwave.jp/qa/q7006239.html

上記の内容はできております。

ただ上記では4チームの設定をしておりますが、
実際は10チームぐらいになります。
上記ではtaisenテーブルに登録されたチームしか表示されないのですが、まだ1試合もおこなっていないチームも表示したいと思っています。
また、それに付け加えて順位の表示もしたいと思っています。
お分かりの方がいらっしゃいましたらご教示お願いします。

例：
順位　|　チーム名　|　勝ち点　|　試合数　|　勝ち数　|　引き分け　|　負け数　|　得点　|　失点　|　得失点差
1　|　C　|　13 |　5　|　4　|　1　|　0　|　11 |　3　|　8
2　|　A　|　6　|　5　|　2　|　0　|　3　|　8　|　12 |　-4
3　|　D　|　4　|　4　|　1　|　1　|　2　|　5　|　7　|　-2
4　|　B　|　2　|　4　|　0　|　2　|　2　|　3　|　7　|　-2
5　|　E　|　0　|　0　|　0　|　0　|　0　|　0　|　0　|　0　
5　|　F　|　0　|　0　|　0　|　0　|　0　|　0　|　0　|　0　
5　|　G　|　0　|　0　|　0　|　0　|　0　|　0　|　0　|　0　
5　|　H　|　0　|　0　|　0　|　0　|　0　|　0　|　0　|　0　
5　|　I　|　0　|　0　|　0　|　0　|　0　|　0　|　0　|　0　
5　|　J　|　0　|　0　|　0　|　0　|　0　|　0　|　0　|　0　

と一覧表示ができればと思っております。
順位の順は勝ち点 > 得失点 > 得点になります。

ぜひ宜しくお願いします。

回答No.3

yambejp · ベストアンサー

前回のモデルを拡張するとこんな感じ

//準備
create table team(team_id int not null primary key,team_name varchar(30));
insert into team values(1,"A"),(2,"B"),(3,"C"),(4,"D"),(5,"E"),(6,"F"),(7,"G"),(8,"H"),(9,"I"),(10,"J");
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),(8,1,9,1);

//表示
select
(select count(*)+1
from (
select team_id
,sum((homepoint>awaypoint)*3 +(homepoint=awaypoint)) as 勝ち点
,sum(homepoint) - sum(awaypoint) as 得失点差
,sum(homepoint) as 得点
from (
select hometeam as team_id,homepoint,awaypoint from taisen
union all select awayteam as team,awaypoint,homepoint from taisen
) as sub1
group by team_id
) as sub2
where
勝ち点>(@a:=coalesce(sum((homepoint>awaypoint)*3 +(homepoint=awaypoint)),0))
or ( 勝ち点=@a and 得失点差>@b:=coalesce((sum(homepoint) - sum(awaypoint)),0))
or ( 勝ち点=@a and 得失点差=@b and 得点>coalesce(sum(homepoint),0))
) as 順位
,team_name チーム名
,coalesce(sum((homepoint>awaypoint)*3 +(homepoint=awaypoint)),0) as 勝ち点
,coalesce(count(sub.team_id),0) as 試合数
,coalesce(sum(homepoint>awaypoint),0) as 勝ち数
,coalesce(sum(homepoint=awaypoint),0) as 引き分け
,coalesce(sum(homepoint<awaypoint),0) as 負け数
,coalesce(sum(homepoint),0) as 得点
,coalesce(sum(awaypoint),0) as 失点
,coalesce(sum(homepoint) - sum(awaypoint),0) as 得失点差
from (
select hometeam as team_id,homepoint,awaypoint from taisen
union all select awayteam as team,awaypoint,homepoint from taisen
) as sub
right join team on sub.team_id=team.team_id
group by team.team_id
order by 勝ち点 desc,得失点差 desc,team.team_id asc;

お礼

yambejp様
お忙しい中有難う御座います。

かなりのSQL文を書かないと実装できないんですね・・・
見たことない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);

select
(select count(*)+1
from (
select team_id
,sum((homepoint>awaypoint)*3 +(homepoint=awaypoint)) as 勝ち点
,sum(homepoint) - sum(awaypoint) as 得失点差
,sum(homepoint) as 得点
from (
select hometeam as team_id,homepoint,awaypoint from taisen
union all select awayteam as team,awaypoint,homepoint from taisen
) as sub1
group by team_id
) as sub2
where
勝ち点>(@a:=coalesce(sum((homepoint>awaypoint)*3 +(homepoint=awaypoint)),0))
or ( 勝ち点=@a and 得失点差>@b:=coalesce((sum(homepoint) - sum(awaypoint)),0))
or ( 勝ち点=@a and 得失点差=@b and 得点>coalesce(sum(homepoint),0))
) as 順位
,year 年度
,leagueid リーグID
,team_name チーム名
,coalesce(sum((homepoint>awaypoint)*3 +(homepoint=awaypoint)),0) as 勝ち点
,coalesce(count(sub.team_id),0) as 試合数
,coalesce(sum(homepoint>awaypoint),0) as 勝ち数
,coalesce(sum(homepoint=awaypoint),0) as 引き分け
,coalesce(sum(homepoint<awaypoint),0) as 負け数
,coalesce(sum(homepoint),0) as 得点
,coalesce(sum(awaypoint),0) as 失点
,coalesce(sum(homepoint) - sum(awaypoint),0) as 得失点差
from (
select hometeam as team_id,homepoint,awaypoint from taisen
union all select awayteam as team,awaypoint,homepoint from taisen
) as sub
right join team on sub.team_id=team.team_id
where
team.year = 2012 and team.leagueid = 2
group by team.team_id
order by 勝ち点 desc,得失点差 desc,team.team_id asc;

上記の内容ですと、順位以外は綺麗にとれています。
ここで順位をまた1位～にすることは可能でしょうか。
yearを2011にしてleaguidを1にしても同じです。

もしお分かりでしたら宜しくお願いします。

回答No.2

yambejp

いくつかやり方がありますが、効率的なデータの持ち方と集計方法の一例です。

//t_playテーブルでgid毎の表裏情報をもっておきます
create table t_play(gid int,omoteura tinyint,tid int,point int, primary key(gid,omoteura));
insert into t_play values(1,0,1,1),(1,1,2,0),(2,0,2,0),(2,1,1,0),(3,0,1,1),(3,1,3,0),(4,0,3,0),(4,1,1,3),(5,0,1,1),(5,1,4,0),(6,0,2,1),(6,1,3,0),(7,0,2,0),(7,1,4,0),(8,0,4,1),(8,1,2,0);

//t_teamテーブルではチームの情報をもちます
create table t_team(tid int primary key,name varchar(30));
insert into t_team values(1,'A'),(2,'B'),(3,'C'),(4,'D'),(5,'E'),(6,'F'),(7,'G'),(8,'H'),(9,'I'),(10,'J');

//で、こんな感じ
select name,勝ち点,試合数,勝ち,分け,負け,得点,失点,得失差
from t_team as t
left join(
select p1.tid
,sum((p1.point>p2.point)*3+(p1.point=p2.point)) as 勝ち点
,count(*) as 試合数
,sum(p1.point>p2.point) as 勝ち
,sum(p1.point=p2.point) as 分け
,sum(p1.point<p2.point) as 負け
,sum(p1.point) as 得点
,sum(p2.point) as 失点
,sum(p1.point)-sum(p2.point) as 得失差
from t_play as p1
inner join t_play as p2 on p1.gid=p2.gid and not(p1.tid = p2.tid)
group by tid
) as sub on t.tid=sub.tid
order by 勝ち点 desc,得失差 desc,得点 desc,t.tid asc;

あとはゲームの詳細用に
create table t_game(gid int,gdatetime datetime,gplace varchar(20));
insert into t_game values(1,'2012-10-01 10:00:00','1丁目球場'),
(2,'2012-10-01 13:00:00','1丁目球場'),
(3,'2012-10-02 10:00:00','2丁目球場'),
(4,'2012-10-02 13:00:00','2丁目球場'),
(5,'2012-10-03 10:00:00','1丁目球場'),
(6,'2012-10-03 10:00:00','3丁目球場'),
(7,'2012-10-04 10:00:00','1丁目球場'),
(8,'2012-10-04 13:00:00','1丁目球場');

みたいなデータを持てば汎用性があがります

お礼

yambejp様、前回同様ご回答有難う御座います。

t_gameにつきましては今回は仕様には考えておらず、前回と同様のSQL文ではできませんでしょうか。
t_teamは上記の内容で大丈夫なのですが、t_playを前回と同様にtaisenと同じテーブルで考えておりました。

教えていただいて大変申し訳ないのですが、以前に教えていただいた内容で可能であれば幸いです。

宜しくお願いします。

回答No.1

shimix

最初にチーム名のテーブルから全件読み込んで、チームIDを添え字に各数値の配列を作っておけばいいのでは？$team = array（）; としておいて

$team[] = array('name'=>$row['name'], 'point'=>0, 'games'=>0・・・・);

といった二次元配列でいいと思います（もちろんクラスを使っても書けるでしょう）。

それから対戦テーブルを読んで、対戦結果に従って（チームIDを添え字に）各数値に加算/減算していけばいいと思います。順位付けはusortを使えば任意の基準で並び替えられますので、それからforeachで一覧を作成すればいいのでは？

http://www.php.net/manual/ja/function.usort.php