どなたか教えてください。
どなたかお分かりの方がいらっしゃいましたらご教授お願いします。
本当に困っております。
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;
とした場合、順位が5位からの表示になってしまいます。
これを1位からの表示にしたいのですが、どのように変更したらよいのかわからず困っており、whereのteam.yearとteam.league.idを変更するたびに順位をリセット(?)する方法を知りたいです。
お分かりの方がいらっしゃいましたらご教授お願いします。
お礼
早速のご回答に感謝致します。 おかげさまで無事解決する事が出来ました。 ありがとうございました！