mysql - 如何使用缺失行的默认值进行 mysql 连接

Question

我有一个表格来跟踪用户的每日分数。它看起来大致是这样的:

CREATE TABLE `DailyScores` (
  `player_id` INTEGER NOT NULL,
  `day_id` INTEGER NOT NULL,
  `score` DOUBLE NOT NULL
);

我也有一个看起来像这样的玩家表:

CREATE TABLE `Players` (
  `player_id` INTEGER NOT NULL,
  `weighted_score` DOUBLE NOT NULL
);

现在球员的weighted_score每天根据球员的历史数据重新计算。我可以分几次执行此操作，如下所示:

-- Clear out the old values
UPDATE Players SET Players.weighted_score = 0;

-- Then several times with different @weight and @day values
UPDATE Players p JOIN DailyScores ds
  ON p.player_id = ds.id 
  WHERE ds.day_id = @day 
  SET p.weighted_score = p.weighted_score + @weight * ds.score;

这处理了某天玩家可能没有得分条目的情况。

但是我想重写 UPDATE 使其更像这样:

UPDATE Players p
  JOIN DailyScores ds1 ON p.player_id = ds1.id 
  JOIN DailyScores ds2 ON p.player_id = ds2.id 
  JOIN DailyScores ds3 ON p.player_id = ds3.id 
  WHERE 
    ds1.day_id = @day1 AND
    ds2.day_id = @day2 AND
    ds3.day_id = @day3 
  SET p.weighted_score = @w1 * ds1.score + @w2 * ds2.score + @w3 * ds3.score;

但我认为如果缺少分数，这将失败。有没有办法让缺失天数的值为 0？

Answer 1

使用 COALESCE() 函数。它返回第一个不是 NULL 的参数。

UPDATE Players p
  LEFT JOIN DailyScores ds1 ON ( p.player_id = ds1.id AND ds1.day_id = @day1)
  LEFT JOIN DailyScores ds2 ON ( p.player_id = ds2.id AND ds2.day_id = @day2)
  LEFT JOIN DailyScores ds3 ON ( p.player_id = ds3.id AND ds3.day_id = @day3)
  SET p.weighted_score = 
     @w1 * COALESCE(ds1.score, 0) +
     @w2 * COALESCE(ds2.score, 0) +
     @w3 * COALESCE(ds3.score, 0);

此外，您还必须执行 LEFT JOIN，因为当玩家未参加比赛时，DailyScores 中可能没有记录。