php项目怎么看两队的主客场战绩差异？

PHP项目如何查看两队的主客场战绩差异

在体育数据类PHP项目中,分析两队主客场战绩差异是很常见的需求，下面从数据设计、查询逻辑、代码实现三个层面说明。

数据表设计

核心是要能区分"主队身份"和"客队身份"，同时记录比赛结果。

-- 比赛表 matches
CREATE TABLE matches (
    id INT PRIMARY KEY,
    home_team_id INT,      -- 主队ID
    away_team_id INT,      -- 客队ID
    home_score INT,        -- 主队得分
    away_score INT,        -- 客队得分
    match_time DATETIME,   -- 比赛时间
    league_id INT,
    status TINYINT         -- 1=已结束
);

关键点：同一支球队在不同比赛中可能是主队也可能是客队，所以要分别统计。

核心SQL查询逻辑

查询某队的主场战绩

SELECT 
    COUNT(*) AS total,
    SUM(home_score > away_score) AS win,
    SUM(home_score = away_score) AS draw,
    SUM(home_score < away_score) AS lose,
    SUM(home_score) AS goals_for,
    SUM(away_score) AS goals_against
FROM matches
WHERE home_team_id = :team_id
  AND status = 1;

查询某队的客场战绩

SELECT 
    COUNT(*) AS total,
    SUM(away_score > home_score) AS win,   -- 注意胜负判断反转
    SUM(away_score = home_score) AS draw,
    SUM(away_score < home_score) AS lose,
    SUM(away_score) AS goals_for,
    SUM(home_score) AS goals_against
FROM matches
WHERE away_team_id = :team_id
  AND status = 1;

一次查询两队主客场差异（推荐）

SELECT
    team_id,
    venue,  -- 'home' 或 'away'
    COUNT(*) AS played,
    SUM(win) AS win,
    SUM(draw) AS draw,
    SUM(lose) AS lose,
    ROUND(SUM(win) / COUNT(*) * 100, 1) AS win_rate
FROM (
    -- 主场比赛
    SELECT home_team_id AS team_id, 'home' AS venue,
           (home_score > away_score) AS win,
           (home_score = away_score) AS draw,
           (home_score < away_score) AS lose
    FROM matches
    WHERE status = 1 AND home_team_id IN (:team_a, :team_b)
    UNION ALL
    -- 客场比赛
    SELECT away_team_id AS team_id, 'away' AS venue,
           (away_score > home_score) AS win,
           (away_score = home_score) AS draw,
           (away_score < home_score) AS lose
    FROM matches
    WHERE status = 1 AND away_team_id IN (:team_a, :team_b)
) t
GROUP BY team_id, venue;

这样一次就能拿到两队各自的主客场数据,再在PHP里做对比。

PHP代码示例（PDO）

function getHomeAwayStats(PDO $pdo, int $teamA, int $teamB): array
{
    $sql = <<<SQL
    SELECT
        team_id,
        venue,
        COUNT(*) AS played,
        COALESCE(SUM(win), 0) AS win,
        COALESCE(SUM(draw), 0) AS draw,
        COALESCE(SUM(lose), 0) AS lose,
        ROUND(SUM(win) / COUNT(*) * 100, 1) AS win_rate
    FROM (
        SELECT home_team_id AS team_id, 'home' AS venue,
               (home_score > away_score) AS win,
               (home_score = away_score) AS draw,
               (home_score < away_score) AS lose
        FROM matches
        WHERE status = 1 AND home_team_id IN (:a, :b)
        UNION ALL
        SELECT away_team_id AS team_id, 'away' AS venue,
               (away_score > home_score) AS win,
               (away_score = home_score) AS draw,
               (away_score < home_score) AS lose
        FROM matches
        WHERE status = 1 AND away_team_id IN (:a, :b)
    ) t
    GROUP BY team_id, venue
    SQL;
    $stmt = $pdo->prepare($sql);
    $stmt->execute([':a' => $teamA, ':b' => $teamB]);
    return $stmt->fetchAll(PDO::FETCH_ASSOC);
}

输出后整理成对比结构：

$stats = getHomeAwayStats($pdo, $teamA, $teamB);
$result = [];
foreach ($stats as $row) {
    $result[$row['team_id']][$row['venue']] = [
        'played'   => (int)$row['played'],
        'win'      => (int)$row['win'],
        'draw'     => (int)$row['draw'],
        'lose'     => (int)$row['lose'],
        'win_rate' => (float)$row['win_rate'],
    ];
}

差异对比维度建议

在看"差异"时，建议对比以下几个指标：

示例对比输出：

foreach ([$teamA, $teamB] as $tid) {
    $home = $result[$tid]['home'] ?? ['win_rate'=>0];
    $away = $result[$tid]['away'] ?? ['win_rate'=>0];
    echo "球队{$tid}: 主场胜率 {$home['win_rate']}% vs 客场胜率 {$away['win_rate']}%\n";
    echo "主客场差异: " . ($home['win_rate'] - $away['win_rate']) . "%\n";
}

注意事项

1. 胜负判断方向：客场统计时，away_score > home_score 才是胜，容易写错。
2. 状态过滤：只统计已结束比赛（status=1），避免未赛/中断比赛污染数据。
3. 时间范围：建议加时间窗口（如近1年或本赛季），避免跨赛季数据失真。
4. 字段为NULL：SUM() 在无数据时返回 NULL，用 COALESCE 兜底。
5. 索引优化：在 home_team_id、away_team_id、status 上加索引，UNION 查询会更快。
6. 赛事级别：不同联赛/杯赛的主客场意义不同，必要时按 league_id 再分组。

如果需要结合双队对阵直接对比（比如A主场 vs B客场的历史交锋），可以在此基础上再加一个 WHERE 条件过滤 (home_team_id=A AND away_team_id=B) OR (...)，做针对性分析。