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

wen PHP项目 2026-11-11 2

PHP 项目中查看两队主客场战绩差异的实现方案

在体育类 PHP 项目中（如足球、篮球赛事系统），分析两队主客场战绩差异是一个常见需求，下面从数据模型、SQL 查询、PHP 代码三个层面说明。

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

数据模型设计

首先需要有比赛结果表,通常包含：

CREATE TABLE matches (
    id INT PRIMARY KEY,
    home_team_id INT,      -- 主队
    away_team_id INT,      -- 客队
    home_score INT,        -- 主队得分
    away_score INT,        -- 客队得分
    match_date DATE,
    status TINYINT         -- 1=已结束
);
CREATE TABLE teams (
    id INT PRIMARY KEY,
    name VARCHAR(50)
);

核心思路

“主客场战绩差异”通常包含两层含义：

某队自身的主场 vs 客场表现差异（主场胜率 - 客场胜率）
两队交锋时，主队的主场战绩与客队的客场战绩对比

下面分别给出 SQL 与 PHP 实现。

SQL 实现

计算某队的主场战绩

SELECT
    COUNT(*) AS total,
    SUM(home_score > away_score) AS wins,
    SUM(home_score = away_score) AS draws,
    SUM(home_score < away_score) AS losses,
    ROUND(SUM(home_score > away_score) / COUNT(*) * 100, 2) AS win_rate
FROM matches
WHERE home_team_id = :team_id AND status = 1;

计算某队的客场战绩

SELECT
    COUNT(*) AS total,
    SUM(away_score > home_score) AS wins,
    SUM(away_score = home_score) AS draws,
    SUM(away_score < home_score) AS losses,
    ROUND(SUM(away_score > home_score) / COUNT(*) * 100, 2) AS win_rate
FROM matches
WHERE away_team_id = :team_id AND status = 1;

一条 SQL 同时算出主客场（推荐）

SELECT
    team_id,
    SUM(is_home) AS home_total,
    SUM(is_home AND is_win) AS home_wins,
    SUM(NOT is_home) AS away_total,
    SUM(NOT is_home AND is_win) AS away_wins
FROM (
    SELECT home_team_id AS team_id, 1 AS is_home,
           (home_score > away_score) AS is_win
    FROM matches WHERE status = 1
    UNION ALL
    SELECT away_team_id AS team_id, 0 AS is_home,
           (away_score > home_score) AS is_win
    FROM matches WHERE status = 1
) t
WHERE team_id IN (:team_a, :team_b)
GROUP BY team_id;

PHP 实现示例（PDO）

<?php
function getHomeAwayStats(PDO $pdo, int $teamId): array
{
    $sql = "
        SELECT
            SUM(is_home = 1) AS home_total,
            SUM(is_home = 1 AND is_win = 1) AS home_wins,
            SUM(is_home = 0) AS away_total,
            SUM(is_home = 0 AND is_win = 1) AS away_wins
        FROM (
            SELECT 1 AS is_home, (home_score > away_score) AS is_win
            FROM matches WHERE home_team_id = :id AND status = 1
            UNION ALL
            SELECT 0 AS is_home, (away_score > home_score) AS is_win
            FROM matches WHERE away_team_id = :id AND status = 1
        ) t
    ";
    $stmt = $pdo->prepare($sql);
    $stmt->execute([':id' => $teamId]);
    $row = $stmt->fetch(PDO::FETCH_ASSOC);
    $homeTotal = (int)($row['home_total'] ?? 0);
    $homeWins  = (int)($row['home_wins']  ?? 0);
    $awayTotal = (int)($row['away_total'] ?? 0);
    $awayWins  = (int)($row['away_wins']  ?? 0);
    $homeRate = $homeTotal ? round($homeWins / $homeTotal * 100, 2) : 0;
    $awayRate = $awayTotal ? round($awayWins / $awayTotal * 100, 2) : 0;
    return [
        'home_total' => $homeTotal,
        'home_wins'  => $homeWins,
        'home_rate'  => $homeRate,
        'away_total' => $awayTotal,
        'away_wins'  => $awayWins,
        'away_rate'  => $awayRate,
        'diff'       => round($homeRate - $awayRate, 2), // 主客场胜率差
    ];
}
// 使用：对比两队
$pdo = new PDO('mysql:host=localhost;dbname=sports', 'user', 'pwd');
$teamA = getHomeAwayStats($pdo, 101);  // 主队
$teamB = getHomeAwayStats($pdo, 202);  // 客队
echo "主队主场胜率: {$teamA['home_rate']}%  客队客场胜率: {$teamB['away_rate']}%\n";
echo "主队主场/客场差: {$teamA['diff']}%\n";
echo "客队主场/客场差: {$teamB['diff']}%\n";

常见业务指标

在实际项目中,除了胜率差，通常还会对比：

指标	说明
胜率差	主场胜率 − 客场胜率
场均进球	`SUM(goals)/COUNT(*)`，主客分别算
场均失球	防守稳定性
净胜球	得失球差
赢盘率	结合让球盘口（博彩类）
N 场（如近 5 场）	用 `ORDER BY match_date DESC LIMIT 5` 单独算

近 N 场加权示例

function getRecentStats(PDO $pdo, int $teamId, int $n = 5): array
{
    $sql = "
        SELECT home_score, away_score, home_team_id
        FROM matches
        WHERE (home_team_id = :id OR away_team_id = :id) AND status = 1
        ORDER BY match_date DESC
        LIMIT :n
    ";
    $stmt = $pdo->prepare($sql);
    $stmt->bindValue(':id', $teamId, PDO::PARAM_INT);
    $stmt->bindValue(':n',  $n,      PDO::PARAM_INT);
    $stmt->execute();
    // 遍历判断该队是主是客、是胜平负……
    return $stmt->fetchAll(PDO::FETCH_ASSOC);
}

前端展示建议

典型“主客场战绩对比”卡片可以这样呈现：

            主队(主)        客队(客)
场次          10              12
胜            7               3
胜率         70%             25%
场均进球      2.1             0.9
场均失球      0.8             1.6
主客差异     +45%            -20%