计算满足条件的行数

Question

我真的认为我的问题不太具体，我只是不知道如何解释。

假设我有一张包含以下列

团队 | 的 表格游戏类型 |日期 |结果

我想创建一个视图，列出有关团队的一些统计信息

SELECT team, count(*) as FROM matches GROUP BY team ORDER BY Total DESC

现在我想添加计数列 胜负

• 联赛比赛总数
• 过去一个月的
• 平局
• 目的
• 。

是在数据库中创建一个视图，而不是任何新的表或列

Answer 1

您可以在聚合函数中使用 case 语句来仅聚合特定事件。

SELECT 
    team, 
    count(*) total_matches,
    count(case when gameType = 'league' then 1 end) league_matches,
    count(case then date > now() - INTERVAL 30 DAY then 1 end) recent_matches,
    ...
FROM matches 
GROUP BY team

Answer 2

总体思路是在聚合内部使用 CASE 表达式

SELECT
    team,
    count(*) as total,
    count(CASE WHEN outcome = 'WIN' THEN 1 END) as wins,
    count(CASE WHEN outcome = 'DRAW' THEN 1 END) as draw,
    count(CASE WHEN outcome = 'LOSE' THEN 1 END) as losses,
    count(CASE WHEN gametype = 'League' THEN 1 END) as leaguegames,
    count(CASE WHEN DATE_SUB(CURDATE(), INTERVAL 1 MONTH) < dateTHEN 1 END) as lastmonth
FROM matches
GROUP BY team 
ORDER BY total DESC

Answer 3

比使用 CASE 更简单、更易读（至少对我来说）是使用 OR：

SELECT
   team,
   count(*) as total,
   count(outcome = 'WIN'  OR NULL) as wins,
   count(outcome = 'DRAW' OR NULL) as draw,
   ...
FROM matches
GROUP BY team