提前查询-选择、按条件求和并返回累计值

将聚集查询与条件查询一起使用 SUM

SELECT
    p.username,
    SUM(CASE WHEN m.match_winner = m.team_a THEN m.team_a_odds ELSE m.team_b_odds END)
FROM 
    predictions p
    INNER JOIN matches m
        ON p.match_id = m.id AND p.team_id = m.match_winner
WHERE p.tournament_id = 30
GROUP BY p.username

SELECT
  USERNAME,
  SUM(IF(CASE WHEN MATCH_WINNER = TEAM_A, TEAM_A_ODDS, TEAM_B_ODDS))
FROM
  PREDICTIONS
INNER JOIN MATCHES
  ON MATCH_ID = MATCHES.ID AND 
    TEAM_ID = MATCH_WINNER
GROUP BY USERNAME;

但是自从 Neville predicted correctly match 7 and 9 (odds 2.4 + 2.0), total 4.4 points

SELECT
  USERNAME,
  SUM( TEAM_B_ODDS)
FROM
  PREDICTIONS
INNER JOIN MATCHES
  ON MATCH_ID = MATCHES.ID AND 
    TEAM_ID = MATCH_WINNER
GROUP BY USERNAME;

Naville