SQL复合条件聚合
SQL复合条件聚合
提问于 2019-10-28 04:38:11
我想计算出PL中每支球队的得分
我有两张桌子
T1
---------------------
TEAM ID || TEAM NAME
---------------------
01 || Liverpool
02 || Man City
---------------------而t2 (例如)
----------------------------------------------------------------
MATCH ID || HOME TEAM || AWAY TEAM || HOME GOALS || AWAY GOALS|
-----------------------------------------------------------------
30 || Liverpool || Man City || 1 || 0
-----------------------------------------------------------------现在计算每场比赛的分数，如果一支球队的进球数大于另一支球队，他赢了3分，输家得到0分，但如果每一支球队都得到相同的进球数，那么他们都是平的，而每个人都只有1分。
新表应该类似于这个
-----------------------------------
Team ID || Team Name || Team Points
------------------------------------
01 || Liverpool || 28
02 || Man City || 22
------------------------------------回答 4
Stack Overflow用户
回答已采纳
发布于 2019-10-28 05:00:55
加入表并使用条件聚合：
select t1.teamid, t1.teamname,
sum(
case sign((homegoals - awaygoals) * case when t1.teamname = t2.hometeam then 1 else -1 end)
when 1 then 3
when 0 then 1
when -1 then 0
end
) teampoints
from t1 inner join t2
on t1.teamname in (t2.hometeam, t2.awayteam)
group by t1.teamid, t1.teamname见演示。
Stack Overflow用户
发布于 2019-10-28 04:55:22
我建议进行横向连接：
select t1.team_id, t1.team_name, sum(v.goals),
sum(case when goals > other_goals then 3
when goals = other_goals then 1
else 0
end) as points
from t2 cross join lateral
(values (t2.home_team, t2.home_goals, t2.away_goals),
(t2.away_team, t2.away_goals, t2.home_goals)
) v(team, goals, other_goals) join
t1
on v.team = t1.team_id
group by t1.team_id, t1.team_name;Stack Overflow用户
发布于 2019-10-28 04:53:52
您可以加入该表并执行条件和：
select
t1.team_id,
t1.team_name,
sum(
case
when t2.home_goals = t2.away_goals then 1
when
( t1.team_name = t2.home_team and t2.home_goals > t2.away_team)
or (t1.team_name = t2.away_team and t2.away_goals > t2.home_team)
then 3
else 0
end
) team_points
from t1
inner join t2 on t1.team_name in (t2.home_team, t2.away_team)
group by t1.team_id, t1.team_name在sum()中，case表达式检查游戏的结果并根据需要分配点数(一场胜利比赛3分，平局1分，输球0分)。
注意:正如注释中所说的，您应该修改您的模式，将团队的ids存储在分数表中，而不是它们的名称中。
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/58591080
复制相关文章
相似问题
腾讯云开发者
