在MySQL中，我有一个表格来保存比赛的结果。该表的结果列包含值“ won”，“ lost”或“ draw”。如何按竞争对手分组并获得特定值的计数？这个想法是能够根据获胜和失败的数量对竞争对手进行排序，并在数据库查询中计算得分。
所以，我的表是：
+--------------+---------+--------+
| CompetitorId | MatchId | Result |
+--------------+---------+--------+
| 1 | 1 | won |
| 2 | 1 | lost |
| 1 | 2 | won |
| 3 | 2 | lost |
+--------------+---------+--------+
我想要得到的结果是：
+--------------+------+--------+
| CompetitorId | Wins | Losses |
+--------------+------+--------+
| 1 | 2 | 0 |
| 2 | 0 | 1 |
| 3 | 0 | 1 |
+--------------+------+--------+
我尝试的查询是这样的：
SELECT CompetitorId, COUNT(result='won') AS wins, COUNT(result='lost') AS losses
FROM match_competitors
GROUP BY CompetitorId
您可以在内部使用case的总和，当需要的列为1时为1，在不需要的情况下为0：
SELECT CompetitorId, sum(case result
when 'won' then 1
else 0
end) as wins,
sum(case result
when 'lost' then 1
else 0
end) as losses
FROM match_competitors
GROUP BY CompetitorId
本文收集自互联网，转载请注明来源。
如有侵权，请联系[email protected] 删除。
我来说两句