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

本文目录导读：

1. 数据库设计
2. 核心SQL查询
3. PHP 业务逻辑封装
4. 使用示例
5. 进阶分析维度
6. 性能优化建议
7. 前端展示建议

在PHP项目中查看两队的主客场战绩差异，通常需要结合数据库设计、数据查询和展示逻辑三个层面来实现,下面我从这几个方面给你一个完整的思路。

数据库设计

通常需要以下核心表：

-- 比赛表
CREATE TABLE matches (
    id INT PRIMARY KEY AUTO_INCREMENT,
    home_team_id INT NOT NULL COMMENT '主队ID',
    away_team_id INT NOT NULL COMMENT '客队ID',
    home_score INT DEFAULT 0,
    away_score INT DEFAULT 0,
    match_date DATETIME,
    status TINYINT DEFAULT 1 COMMENT '1已结束 0未开始',
    league_id INT
);
-- 球队表
CREATE TABLE teams (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50)
);

核心SQL查询

查询某队的主场战绩

SELECT 
    COUNT(*) AS total,
    SUM(CASE WHEN home_score > away_score THEN 1 ELSE 0 END) AS win,
    SUM(CASE WHEN home_score = away_score THEN 1 ELSE 0 END) AS draw,
    SUM(CASE WHEN home_score < away_score THEN 1 ELSE 0 END) 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(CASE WHEN away_score > home_score THEN 1 ELSE 0 END) AS win,
    SUM(CASE WHEN away_score = home_score THEN 1 ELSE 0 END) AS draw,
    SUM(CASE WHEN away_score < home_score THEN 1 ELSE 0 END) 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 
    t.id AS team_id,
    t.name AS team_name,
    'home' AS venue,
    COUNT(*) AS played,
    SUM(CASE WHEN m.home_score > m.away_score THEN 1 ELSE 0 END) AS win,
    SUM(CASE WHEN m.home_score = m.away_score THEN 1 ELSE 0 END) AS draw,
    SUM(CASE WHEN m.home_score < m.away_score THEN 1 ELSE 0 END) AS lose
FROM matches m
JOIN teams t ON t.id = m.home_team_id
WHERE m.home_team_id IN (:team_a, :team_b) AND m.status = 1
GROUP BY m.home_team_id
UNION ALL
SELECT 
    t.id,
    t.name,
    'away',
    COUNT(*),
    SUM(CASE WHEN m.away_score > m.home_score THEN 1 ELSE 0 END),
    SUM(CASE WHEN m.away_score = m.home_score THEN 1 ELSE 0 END),
    SUM(CASE WHEN m.away_score < m.home_score THEN 1 ELSE 0 END)
FROM matches m
JOIN teams t ON t.id = m.away_team_id
WHERE m.away_team_id IN (:team_a, :team_b) AND m.status = 1
GROUP BY m.away_team_id;

PHP 业务逻辑封装

class TeamStats
{
    private PDO $pdo;
    public function __construct(PDO $pdo)
    {
        $this->pdo = $pdo;
    }
    /**
     * 获取球队主/客场战绩
     */
    public function getVenueStats(int $teamId, string $venue = 'home'): array
    {
        if ($venue === 'home') {
            $sql = "SELECT 
                        COUNT(*) AS played,
                        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 gf,
                        SUM(away_score) AS ga
                    FROM matches
                    WHERE home_team_id = :tid AND status = 1";
        } else {
            $sql = "SELECT 
                        COUNT(*) AS played,
                        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 gf,
                        SUM(home_score) AS ga
                    FROM matches
                    WHERE away_team_id = :tid AND status = 1";
        }
        $stmt = $this->pdo->prepare($sql);
        $stmt->execute([':tid' => $teamId]);
        $row = $stmt->fetch(PDO::FETCH_ASSOC);
        $played = (int)$row['played'];
        $row['points'] = $row['win'] * 3 + $row['draw'];
        $row['goal_diff'] = $row['gf'] - $row['ga'];
        $row['win_rate'] = $played ? round($row['win'] / $played * 100, 1) : 0;
        return $row;
    }
    /**
     * 两队主客场差异对比
     */
    public function compare(int $teamA, int $teamB): array
    {
        return [
            'team_a' => [
                'home' => $this->getVenueStats($teamA, 'home'),
                'away' => $this->getVenueStats($teamA, 'away'),
            ],
            'team_b' => [
                'home' => $this->getVenueStats($teamB, 'home'),
                'away' => $this->getVenueStats($teamB, 'away'),
            ],
        ];
    }
}

使用示例

$stats = new TeamStats($pdo);
$result = $stats->compare(1, 2);
// 输出：主队主场胜率 vs 客队客场胜率
$homeWinRate = $result['team_a']['home']['win_rate'];
$awayWinRate = $result['team_b']['away']['win_rate'];
echo "主队主场胜率：{$homeWinRate}%\n";
echo "客队客场胜率：{$awayWinRate}%\n";
// 差异分析
$diff = $homeWinRate - $awayWinRate;
if ($diff > 20) {
    echo "主队主场优势明显\n";
} elseif ($diff < -20) {
    echo "客队客场表现更好\n";
} else {
    echo "两队实力接近\n";
}

进阶分析维度

实际项目中,主客场差异分析通常还会结合：

加入时间过滤的示例

WHERE home_team_id = :tid 
  AND status = 1
  AND match_date >= DATE_SUB(NOW(), INTERVAL 1 YEAR)

性能优化建议

1. 建立索引：(home_team_id, status)、(away_team_id, status)、match_date
2. 缓存统计结果：用 Redis 缓存 5-30 分钟，避免频繁聚合
3. 物化视图/汇总表：数据量大时，定时任务预计算 team_venue_stats 汇总表
4. 分页/筛选：支持按赛季、联赛筛选，避免全表扫描

前端展示建议

用表格或雷达图对比：

             主队(主场)   客队(客场)
场次            15           14
胜/平/负       10/3/2       5/4/5
胜率           66.7%        35.7%
场均进球        1.8          1.2
场均失球        0.7          1.5

如果你能告诉我具体的项目场景（比如是足球还是篮球、数据表结构、用的框架）,我可以给出更贴合的代码实现。