请求创建英超球队场均进球/失球统计视图的SQL技术支持

问题诊断 #

你的原代码核心逻辑（主客场进球/失球的取值）是正确的，但存在三个关键问题：

1. 列别名不符合要求，未匹配Team、AvgGoalsScoredPerGame等指定字段名
2. 仅用ROUND()无法确保始终显示两位小数（部分数据库会省略末尾的0，比如显示2.3而非2.30）
3. 使用JOIN会过滤掉无参赛记录的球队，统计不完整

修正后的SQL代码 #

CREATE VIEW EPLTeamStats2023 AS
SELECT
    t.Team,
    -- 强制保留两位小数，适配多数数据库
    CAST(ROUND(AVG(m.goals_scored), 2) AS DECIMAL(5, 2)) AS AvgGoalsScoredPerGame,
    CAST(ROUND(AVG(m.goals_conceded), 2) AS DECIMAL(5, 2)) AS AvgGoalsConcededPerGame,
    -- 用COUNT(m.Team)统计有效场次，避免无比赛球队被误计
    COUNT(m.Team) AS TotalMatchesPlayed
FROM
    EPLTeams2023 t
-- 用LEFT JOIN确保所有球队都出现在结果中
LEFT JOIN (
    -- 合并主客场数据，避免重复编写筛选条件
    SELECT HomeTeam AS Team, HomeScore AS goals_scored, AwayScore AS goals_conceded, MatchDate, HomeTeam, AwayTeam
    FROM Matches_Duplicate
    UNION ALL
    SELECT AwayTeam AS Team, AwayScore AS goals_scored, HomeScore AS goals_conceded, MatchDate, HomeTeam, AwayTeam
    FROM Matches_Duplicate
) m ON t.Team = m.Team
WHERE
    -- 统一筛选有效比赛记录
    m.HomeTeam IS NOT NULL
    AND m.AwayTeam IS NOT NULL
    AND m.HomeScore IS NOT NULL
    AND m.AwayScore IS NOT NULL
    AND m.MatchDate IS NOT NULL
    AND m.MatchDate >= '2023-08-01'
GROUP BY
    t.Team;

关键细节说明 #

• 小数显示：CAST(ROUND(..., 2) AS DECIMAL(5,2))会强制输出两位小数（如2.34、1.50），如果是MySQL环境，也可以用FORMAT(AVG(m.goals_scored), 2)直接格式化；SQL Server则可替换为CAST(ROUND(...) AS NUMERIC(5,2))。
• 场次统计：COUNT(m.Team)会忽略LEFT JOIN产生的NULL值，准确统计球队实际参赛场次，无比赛的球队场次会显示为0。
• 查询效率：将重复的筛选条件移至外层，避免子查询重复执行过滤逻辑，提升性能。

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