php - 3 字段求和但仅当有值时

Question

这可能是理所当然的。我有 table

游戏:

guid(PK), puid, gScoreA, gTimesPlayed, gScoreB.

玩家:

puid(PK), pFname, pLname

我正在尝试:

Select 
    player.puid, 
    (SUM(game.gScoreA)/SUM(game.gTimesPlayed)) as skillA, 
    (SUM(game.gScoreB)/SUM(game.gTimesPlayed)) as skillB 
FROM 
    player, 
    game 
WHERE 
    player.puid = game.puid 
ORDER BY 
    skillA DESC, 
    skillB DESC

因此，在每场比赛中，玩家可以拥有 gScoreA 或 gScoreB，并且始终有 gTimesPlayed。因此，我希望所有 SUM(game.gScoreA) 仅除以 SUM(game.gTimesPlayed)，其中 game.gScoreA 具有值，并且 game.gScoreB 不会，反之亦然，对于 SUM(game.gScoreB)/SUM(game.gTimesPlayed) WHERE gScoreA没有值(value) - 作为玩家数据库中每个玩家的总数......我的耳朵里冒出烟雾和 Steam !!

Answer 1

SELECT 
    player.puid, 
    SUM(game.gScoreA)/SUM(IF(game.gScoreA IS NOT NULL, game.gTimesPlayed, 0)) as skillA, 
    SUM(game.gScoreB)/SUM(IF(game.gScoreB IS NOT NULL, game.gTimesPlayed, 0)) as skillB 
FROM player INNER JOIN game ON player.puid = game.puid 
GROUP BY player.puid
ORDER BY 
    skillA DESC, 
    skillB DESC

使用GROUP BY获取每个玩家的结果。

此外，请使用 JOIN 语法而不是逗号样式的连接。