满足条件的行数
问题描述:
我真的认为我的问题不是太具体,我只是不知道如何解释它。满足条件的行数
说我有一个表,下面Collumns
team | gameType | date | outcome
我想创建列出有关球队的一些统计数据的视图
SELECT team, count(*) as FROM matches GROUP BY team ORDER BY total DESC
现在我想添加列计算总数
- 这是场联赛
- 已经在过去的一个月
- 胜
- 损失
- 关系
目的是在数据库中,没有任何新的表或列创建一个视图。
答
您可以在聚合函数中使用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
答
总的想法是使用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
答
有点更简单和更可读内部(至少对我来说)比使用的情况是使用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
CASE是我在找的,谢谢! – Moak
@Konerak:没有。对于缺少的“ELSE”条件,COUNT(NULL)将被忽略 – gbn