MySQL 5.7中关联两表实现球队进球数排名的方案求助

核心思路 #

利用MySQL用户变量在单语句内模拟RANK()窗口函数的逻辑，同时关联team_data和team_stats表，按联赛分组计算进球效率（GF=G/GP）的排名，适配PHP无法执行多语句的限制。

实现SQL语句 #

SELECT 
    team_id,
    team_name,
    league_id,
    goals,
    games_played,
    gf_efficiency,
    @rank := CASE
        WHEN @prev_league != league_id THEN 1
        WHEN @prev_gf = gf_efficiency THEN @rank
        ELSE @rank + 1
    END AS rank,
    @prev_league := league_id,
    @prev_gf := gf_efficiency
FROM (
    -- 先关联表并计算进球效率，按联赛和效率降序排序
    SELECT 
        td.team_id,
        td.team_name,
        ts.league_id,
        ts.G AS goals,
        ts.GP AS games_played,
        -- 处理场次为0的情况，避免除以0
        CASE 
            WHEN ts.GP = 0 THEN 0 
            ELSE ROUND(ts.G / ts.GP, 2) 
        END AS gf_efficiency
    FROM team_data td
    JOIN team_stats ts ON td.team_id = ts.team_id
    ORDER BY ts.league_id, gf_efficiency DESC, td.team_name
) AS ranked_teams,
-- 初始化变量，单语句完成所有逻辑
(SELECT @prev_league := NULL, @prev_gf := NULL, @rank := 0) AS init_vars

关键细节说明 #

• 排名逻辑：完全模拟MySQL 8.0的RANK()函数行为——同一联赛内进球效率相同的球队排名并列，后续名次跳过并列数量（例如：1,1,3）
• 变量初始化：在FROM子句中完成变量初始化，确保整个查询是单语句，PHP可直接执行
• 场次为0处理：通过CASE语句将GP=0的球队进球效率设为0，避免除法错误，也可根据需求改为NULL
• 排序规则：子查询先按联赛分组，再按进球效率降序，最后按球队名称排序，保证排名的稳定性

PHP执行示例（mysqli） #

$dbHost = '你的主机地址';
$dbUser = '数据库用户名';
$dbPass = '数据库密码';
$dbName = '冰球数据库名';

$conn = new mysqli($dbHost, $dbUser, $dbPass, $dbName);
if ($conn->connect_error) {
    die("连接失败: " . $conn->connect_error);
}

$sql = "上面的完整SQL语句";
$result = $conn->query($sql);

if ($result->num_rows > 0) {
    while ($row = $result->fetch_assoc()) {
        echo "联赛ID: {$row['league_id']} | 球队: {$row['team_name']} | 排名: {$row['rank']} | 进球数: {$row['goals']} | 场次: {$row['games_played']} | 进球效率: {$row['gf_efficiency']}<br>";
    }
} else {
    echo "无数据";
}
$conn->close();

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