MySQL，过滤但计数不同的值

Question

我正在编写一个简单的足球数据库，在该数据库中，我希望球队在欧足联比赛，但计算欧足联的胜利次数。

球队可以参加欧足联比赛，结果应该是“赢”“平”或“输”

为此，我编写了此查询，但我不知道应该如何更改计数语句，因为此查询给出了在 UEFA 中比赛的每支球队的比赛数量。

SELECT t.name, count(*) as Wins
FROM Teams t, Matches m
WHERE competion = 'UEFA' AND t.name = m.team
GROUP BY t.name

Answer 1

根据适当的标准，它看起来像:

SELECT t.name, count(*) as matches,
sum(case when result = "win" then 1 else 0 end) as wins,
sum(case when result = "draw" then 1 else 0 end) as draws,
sum(case when result = "loss" then 1 else 0 end) as losses
FROM Teams t, Matches m
WHERE competion = 'UEFA' AND t.name = m.team
GROUP BY t.name