mysql - 在mysql中使用case和limit对结果进行计数/求和

Question

尝试创建一个查询来给出总胜、平和负。我有以下查询

SELECT CASE 
    WHEN m.home_team = '192'
        AND m.home_full_time_score > m.away_full_time_score
        OR m.away_team = '192'
        AND m.home_full_time_score < m.away_full_time_score
        THEN 1
    END AS 'wins',
CASE 
    WHEN m.home_team = '192'
        AND m.home_full_time_score = m.away_full_time_score
        OR m.away_team = '192'
        AND m.home_full_time_score = m.away_full_time_score
        THEN 1
    END AS 'draws',
CASE 
    WHEN m.home_team = '192'
        AND m.home_full_time_score < m.away_full_time_score
        OR m.away_team = '192'
        AND m.home_full_time_score > m.away_full_time_score
        THEN 1
    END AS 'loss'
FROM exp_tm_matches m
INNER JOIN exp_tm_division_matches dm
    ON (dm.match = m.id)
INNER JOIN exp_tm_divisions d
    ON (dm.division = d.id)
WHERE m.info = 'PL'
    AND (
        m.home_team = '192'
        OR m.away_team = '192'
        )
    AND dm.season = '16'
ORDER BY m.kick_off DESC LIMIT 5

这会产生以下内容

wins        draws       loses   
NULL        1           NULL
1           NULL        NULL
1           NULL        NULL
Null        1           NULL
1           NULL        NULL

我想要得到的是赢 = 3 平局 = 2 输 = 0，如果我将计数添加到 case 语句中，它会忽略 5 的 LIMIT。

Answer 1

使用条件聚合。你就快到了:

SELECT SUM( (m.home_team = 192 AND m.home_full_time_score > m.away_full_time_score) OR
            (m.away_team = 192 AND m.home_full_time_score < m.away_full_time_score
            )
          ) as wins,
       SUM(m.home_full_time_score = m.away_full_time_score) 
          ) as draws,
       SUM( (m.home_team = 192 AND m.home_full_time_score < m.away_full_time_score) OR
            (m.away_team = 192 AND m.home_full_time_score > m.away_full_time_score
            )
          ) as losses
FROM exp_tm_matches m INNER JOIN
     exp_tm_division_matches dm
     ON dm.match = m.id
WHERE m.info = 'PL' AND
      192 IN (m.home_team, m.away_team) AND
      dm.season = 16;

注释:

• 您不使用除法表，因此我删除了该连接。
• 如果 id 是数字，则不应在常量两边使用引号。
• 不应对列别名使用单引号。这些名称不需要任何转义字符，因此我删除了它们。事实上，仅对字符串和日期常量使用单引号。
• MySQL 有一个很好的功能， bool 值可以被视为 0 表示 false，1 表示 true，从而无需使用 if() 或 case。
• 抽牌的逻辑不需要查看团队代码。
• 您只需要返回一行包含摘要信息，因此不需要限制。

要获取最后五个匹配项，请将 from 和 where 子句更改为:

FROM (SELECT m.*
      FROM exp_tm_matches m INNER JOIN
           exp_tm_division_matches dm
           ON dm.match = m.id
      WHERE m.info = 'PL' AND
            192 IN (m.home_team, m.away_team) AND
            dm.season = 16
      ORDER BY m.kick_off DESC
      LIMIT 5
     ) m