MySQL求和忽略NULL值问题排查及SQL语句修正请求

I see the problem here—your current query relies on a subquery that fetches the third-highest score using LIMIT 2,1, but when a player has fewer than three valid scores (like player 5 with only one non-zero score), this subquery returns NULL. The CASE statement then can't match any rows, so SUM() ends up returning NULL instead of summing the available top scores.

Here's a more robust solution using window functions to rank each player's scores, which handles cases where there are fewer than three scores gracefully:

WITH RankedScores AS (
    SELECT 
        idPlayerDetails,
        RoundScorecardPlayerPoints,
        -- Rank scores from highest to lowest; use RANK() instead if ties should share ranks
        ROW_NUMBER() OVER (
            PARTITION BY idPlayerDetails 
            ORDER BY RoundScorecardPlayerPoints DESC, idRoundScorecard DESC
        ) AS ScoreRank
    FROM RoundScorecard
)
SELECT 
    y.idPlayerDetails,
    IFNULL(MIN(CASE WHEN y.CompetitionRoundID = 1 THEN y.RoundScorecardPlayerPoints END), 0) AS Rnd1,
    IFNULL(MIN(CASE WHEN y.CompetitionRoundID = 2 THEN y.RoundScorecardPlayerPoints END), 0) AS Rnd2,
    IFNULL(MIN(CASE WHEN y.CompetitionRoundID = 3 THEN y.RoundScorecardPlayerPoints END), 0) AS Rnd3,
    IFNULL(MIN(CASE WHEN y.CompetitionRoundID = 4 THEN y.RoundScorecardPlayerPoints END), 0) AS Rnd4,
    IFNULL(MIN(CASE WHEN y.CompetitionRoundID = 5 THEN y.RoundScorecardPlayerPoints END), 0) AS Rnd5,
    IFNULL(MIN(CASE WHEN y.CompetitionRoundID = 6 THEN y.RoundScorecardPlayerPoints END), 0) AS Rnd6,
    IFNULL(MIN(CASE WHEN y.CompetitionRoundID = 7 THEN y.RoundScorecardPlayerPoints END), 0) AS Rnd7,
    -- Sum the top 3 ranked scores; IFNULL ensures we get 0 instead of NULL if no scores exist
    IFNULL(SUM(rs.RoundScorecardPlayerPoints), 0) AS TopThree
FROM RoundScorecard y
LEFT JOIN RankedScores rs 
    ON y.idPlayerDetails = rs.idPlayerDetails 
    AND rs.ScoreRank <= 3
GROUP BY y.idPlayerDetails;

Why this works: #

• CTE for Ranking: The RankedScores CTE assigns a unique rank to each score for every player, starting at 1 for the highest score. This works even if a player has only 1 or 2 scores.
• Filter Top 3: We join back to this ranked data and only include scores where the rank is 1, 2, or 3.
• Handle NULLs: IFNULL() around the SUM() ensures that if a player has no valid scores (though unlikely in your case), we return 0 instead of NULL.

For player 5, this will correctly sum their single score of 15 as the TopThree value, since it's ranked 1 and included in the sum.

内容的提问来源于stack exchange，提问作者ereceles