我一直在试图找到关于这个话题的东西,我似乎找不到任何东西,这里有几个问题,但是他们没有为我的特定项目工作.
我问了一个类似的问题,关于更新表,但它不会为我实际想要的工作
这里是结果列表.
-------------------------------------------------------- |id | hometeam |goalsfor|goalsagainst| awayteam | -------------------------------------------------------- | 1 |Inter Milan | 3 | 1 | FC Barcelona | -------------------------------------------------------- | 2 |FC Barcelona | 1 | 0 | Inter Milan | -------------------------------------------------------- | 3 |Inter Milan | 4 | 0 | AC Milan | -------------------------------------------------------- | 4 |AC Milan | 0 | 2 | Inter Milan | -------------------------------------------------------- | 5 |Real Madrid | 2 | 0 | AC Milan | -------------------------------------------------------- | 6 |AC Milan | 2 | 2 | Real Madrid | -------------------------------------------------------- | 7 |FC Barcelona | 2 | 2 | AC Milan | -------------------------------------------------------- | 8 |Real Madrid | 2 | 0 | Inter Milan | -------------------------------------------------------- | 9 |Inter Milan | 3 | 1 | Real Madrid | -------------------------------------------------------- | 10 |FC Barcelona | 2 | 0 | Real Madrid | -------------------------------------------------------- | 11 |Real Madrid | 1 | 1 | FC Barcelona | --------------------------------------------------------
基本上我想要能够创建一个排序表排列队伍的顺序,我想提出这个表,而不是放在数据库
Pos Team Pld W D L F A GD Pts 1 FC Barcelona 5 2 3 0 8 5 3 9 2 Inter Milan 6 2 2 2 11 10 1 8 3 Real Madrid 6 2 2 2 8 8 0 8 4 AC Milan 5 0 3 2 8 12 -4 3
POS =位置W =赢得D =绘制L =损失F =进球进球A =目标进球数=目标差Pts =积分
我认为最有效的方法是分配胜利,抽签和损失,总计得分的目标和得分的目标,以及当回应数据时 – 计算玩的总数和点数.
但是我如何分配胜利或损失？并计算目标得分和目标？
首先联合分数表一起交换hometeam与远程和交换目标数.这给你一些容易聚合的源数据,生成记分卡的查询是这样的：
select
team,count(*) played,count(case when goalsfor > goalsagainst then 1 end) wins,count(case when goalsagainst> goalsfor then 1 end) lost,count(case when goalsfor = goalsagainst then 1 end) draws,sum(goalsfor) goalsfor,sum(goalsagainst) goalsagainst,sum(goalsfor) - sum(goalsagainst) goal_diff,sum(
case when goalsfor > goalsagainst then 3 else 0 end
+ case when goalsfor = goalsagainst then 1 else 0 end
) score
from (
select hometeam team,goalsfor,goalsagainst from scores
union all
select awayteam,goalsagainst,goalsfor from scores
) a
group by team
order by score desc,goal_diff desc;