Apex飞镖联赛数据库：基于团队平均分计算让分的SQL需求

AI 小北

2026-8-8

飞镖联赛团队统计及让分计算SQL实现

我有一个用于飞镖联赛的Apex数据库，已编写SQL计算每位玩家的比赛数、总积分及平均得分，语句如下：

select Team, name,
       COUNT(WEEK) * 3 GAMES,
       sum(game_1) + sum(game_2) + sum(game_3) points,
       Round(((sum(game_1) + sum(game_2) + sum(game_3)) / ((COUNT(WEEK) * 3))), 2) Average
from score_tbl
group by Team, name
order by 1

执行后得到玩家层面的统计结果：

TEAM	NAME	GAMES	POINTS	AVERAGE
1	B Tyler	6	142	23.67
1	Blind	6	108	18
1	Jim V	6	53	8.83
1	KC M	6	82	13.67
2	J Spass	6	102	17
2	Randy B	6	105	17.5
2	Tim Ketz	6	74	12.33
2	Todd Lapan	6	51	8.5

需要实现两个目标：

按团队汇总玩家平均得分（如Team1总平均64.17，Team2总平均55.33）
找出最高团队平均分，通过公式「最高平均分 - 各团队平均分 × 90%」计算各团队让分

解决方案

1. 计算团队玩家平均得分总和

通过CTE先获取每个玩家的平均得分，再按团队求和：

WITH player_stats AS (
    SELECT 
        Team,
        Round(((sum(game_1) + sum(game_2) + sum(game_3)) / ((COUNT(WEEK) * 3))), 2) Average
    FROM score_tbl
    GROUP BY Team, name
)
SELECT 
    Team,
    Round(SUM(Average), 2) AS Team_Total_Average
FROM player_stats
GROUP BY Team
ORDER BY Team;

执行结果：

Team	Team_Total_Average
1	64.17
2	55.33

2. 计算各团队让分

在上述基础上，新增CTE获取最高团队平均分，再套用公式计算让分：

WITH player_stats AS (
    SELECT 
        Team,
        Round(((sum(game_1) + sum(game_2) + sum(game_3)) / ((COUNT(WEEK) * 3))), 2) Average
    FROM score_tbl
    GROUP BY Team, name
),
team_totals AS (
    SELECT 
        Team,
        Round(SUM(Average), 2) AS Team_Total_Average
    FROM player_stats
    GROUP BY Team
),
max_team_avg AS (
    SELECT MAX(Team_Total_Average) AS Highest_Average
    FROM team_totals
)
SELECT 
    tt.Team,
    tt.Team_Total_Average,
    Round(mta.Highest_Average - tt.Team_Total_Average * 0.9, 2) AS Handicap
FROM team_totals tt
CROSS JOIN max_team_avg mta
ORDER BY tt.Team;

执行结果：