本文目录导读：
在PHP项目中实现"两队历史交锋记录"的查看功能，核心是数据存储设计 + 查询逻辑 + 展示,下面我给你一套完整的思路和代码示例。
数据库设计
方案1：直接存比赛表（推荐）
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 NOT NULL DEFAULT 0,
away_score INT NOT NULL DEFAULT 0,
match_time DATETIME NOT NULL COMMENT '比赛时间',
league_id INT COMMENT '联赛ID',
status TINYINT DEFAULT 1 COMMENT '1已结束 0未开始',
INDEX idx_teams (home_team_id, away_team_id),
INDEX idx_time (match_time)
);
CREATE TABLE teams (
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(50) NOT NULL,
logo VARCHAR(255)
);
方案2：单独建交锋记录表（数据量大时优化）
如果需要频繁查询交锋，可以建一张冗余表 team_confrontations，比赛结束后异步更新,查询更快。
核心查询逻辑
关键点：两队可能互为对手，A主场B和B主场A都算一次交锋，所以要用 OR 条件。
<?php
class ConfrontationService
{
private PDO $pdo;
public function __construct(PDO $pdo)
{
$this->pdo = $pdo;
}
/**
* 获取两队历史交锋记录
*/
public function getHistory(int $teamA, int $teamB, int $limit = 10): array
{
$sql = "SELECT
m.id,
m.home_team_id,
m.away_team_id,
m.home_score,
m.away_score,
m.match_time,
ht.name AS home_name,
at.name AS away_name,
l.name AS league_name
FROM matches m
LEFT JOIN teams ht ON ht.id = m.home_team_id
LEFT JOIN teams at ON at.id = m.away_team_id
LEFT JOIN leagues l ON l.id = m.league_id
WHERE m.status = 1
AND (
(m.home_team_id = :a AND m.away_team_id = :b)
OR (m.home_team_id = :b AND m.away_team_id = :a)
)
ORDER BY m.match_time DESC
LIMIT :limit";
$stmt = $this->pdo->prepare($sql);
$stmt->bindValue(':a', $teamA, PDO::PARAM_INT);
$stmt->bindValue(':b', $teamB, PDO::PARAM_INT);
$stmt->bindValue(':limit', $limit, PDO::PARAM_INT);
$stmt->execute();
return $stmt->fetchAll(PDO::FETCH_ASSOC);
}
}
统计胜负平数据
除了列出每场，通常还需要展示"X胜Y平Z负"的统计数据。
public function getStats(int $teamA, int $teamB): array
{
$sql = "SELECT
home_team_id,
away_team_id,
home_score,
away_score
FROM matches
WHERE status = 1
AND (
(home_team_id = :a AND away_team_id = :b)
OR (home_team_id = :b AND away_team_id = :a)
)";
$stmt = $this->pdo->prepare($sql);
$stmt->execute([':a' => $teamA, ':b' => $teamB]);
$rows = $stmt->fetchAll(PDO::FETCH_ASSOC);
$stats = [
'total' => 0,
'team_a_win' => 0,
'team_b_win' => 0,
'draw' => 0,
'a_goals' => 0,
'b_goals' => 0,
'a_home_win' => 0, // A主场胜
'a_home_draw' => 0,
'a_home_lose' => 0,
];
foreach ($rows as $r) {
$stats['total']++;
// 判断A和B在这场比赛中的得分
if ($r['home_team_id'] == $teamA) {
$aScore = $r['home_score'];
$bScore = $r['away_score'];
$isAHome = true;
} else {
$aScore = $r['away_score'];
$bScore = $r['home_score'];
$isAHome = false;
}
$stats['a_goals'] += $aScore;
$stats['b_goals'] += $bScore;
if ($aScore > $bScore) {
$stats['team_a_win']++;
if ($isAHome) $stats['a_home_win']++;
} elseif ($aScore < $bScore) {
$stats['team_b_win']++;
if ($isAHome) $stats['a_home_lose']++;
} else {
$stats['draw']++;
if ($isAHome) $stats['a_home_draw']++;
}
}
return $stats;
}
控制器调用示例（如 ThinkPHP / Laravel 风格）
public function history($teamAId, $teamBId)
{
$service = new ConfrontationService($this->pdo);
$list = $service->getHistory($teamAId, $teamBId, 10);
$stats = $service->getStats($teamAId, $teamBId);
// 输出 JSON（API 接口）
return json_encode([
'code' => 0,
'data' => [
'stats' => $stats,
'list' => $list,
],
], JSON_UNESCAPED_UNICODE);
}
性能优化建议
| 问题 | 优化方案 |
|---|---|
| 数据量大查询慢 | 加联合索引 (home_team_id, away_team_id, match_time) |
| 高频查询 | Redis 缓存结果，key 形如 confront:{小id}:{大id} |
| 交锋数据复杂 | 单独建 team_confrontations 汇总表，定时/触发更新 |
| 分页展示 | 列表用 LIMIT + OFFSET 或游标分页 |
缓存示例
$cacheKey = "confront:" . min($a,$b) . ":" . max($a,$b);
$data = $redis->get($cacheKey);
if (!$data) {
$data = $service->getStats($a, $b);
$redis->setex($cacheKey, 3600, json_encode($data));
}
前端展示结构建议
┌─────────────────────────────────┐
│ 近10次交锋：A队 4胜 3平 3负 │
│ 总进球：A 15 - 12 B │
├─────────────────────────────────┤
│ 2024-05-01 A队 2:1 B队 英超 │
│ 2024-01-15 B队 0:0 A队 英超 │
│ ... │
└─────────────────────────────────┘- 查询条件必须双向匹配（
OR）,否则漏数据。 - 归一化处理：把"A视角"的比分算出来,统计胜平负。
- 索引 + 缓存是高并发的关键。
- 如果项目小，直接在
matches表查；如果数据巨大,加汇总表。
如果你用的是特定框架（ThinkPHP、Laravel、Hyperf 等）或需要我补充分页、缓存、接口签名等部分，告诉我具体场景,我可以给到完整的可运行代码。
