Oracle SQL：如何正确统计各球队总进球数？查询结果异常求助

数据老陈

2026-5-28

解决Oracle中球队总进球数统计为空的问题

你的问题核心在于当球队没有对应获胜/对手记录时，SUM()函数会返回NULL，而NULL与任何值相加的结果依然是NULL，这就导致部分球队的总进球数显示为空。下面提供两种可靠的修复方案：

方法一：用NVL()处理NULL值

直接将每个子查询的SUM结果用NVL()转换为0，确保即使没有对应记录也不会返回NULL：

SELECT 
    name,
    NVL((SELECT SUM(opponentscore) FROM matches WHERE opponent_id = teams.id), 0) +
    NVL((SELECT SUM(winnerscore) FROM matches WHERE winner_id = teams.id), 0) AS total_goals
FROM teams;

方法二：改用LEFT JOIN聚合统计（更高效）

这种方式仅需扫描一次matches表，在数据量较大时性能更优，同时也能自然处理无记录的情况：

SELECT
    t.name,
    COALESCE(SUM(CASE WHEN m.winner_id = t.id THEN m.winnerscore ELSE 0 END) +
             SUM(CASE WHEN m.opponent_id = t.id THEN m.opponentscore ELSE 0 END), 0) AS total_goals
FROM teams t
LEFT JOIN matches m 
    ON t.id = m.winner_id OR t.id = m.opponent_id
GROUP BY t.id, t.name
ORDER BY t.id;

如果想让逻辑更清晰，也可以拆分进球来源再聚合：

SELECT
    t.name,
    COALESCE(SUM(goals), 0) AS total_goals
FROM teams t
LEFT JOIN (
    -- 提取所有获胜方的进球记录
    SELECT winner_id AS team_id, winnerscore AS goals FROM matches
    UNION ALL
    -- 提取所有对手方的进球记录
    SELECT opponent_id AS team_id, opponentscore AS goals FROM matches
) all_goals ON t.id = all_goals.team_id
GROUP BY t.id, t.name
ORDER BY t.id;