php项目统计射门次数哪队更多？

PHP 项目：统计哪队射门次数更多

下面给你一个完整的实现方案，从数据库设计到统计逻辑再到代码实现。

数据库设计

方案1：基于事件表（推荐，最灵活）

-- 比赛表
CREATE TABLE matches (
    id INT PRIMARY KEY AUTO_INCREMENT,
    home_team VARCHAR(50),
    away_team VARCHAR(50),
    match_time DATETIME
);
-- 射门事件表（每次射门一条记录）
CREATE TABLE shots (
    id INT PRIMARY KEY AUTO_INCREMENT,
    match_id INT NOT NULL,
    team VARCHAR(50) NOT NULL,      -- 射门所属球队
    player VARCHAR(50),
    minute INT,
    is_on_target TINYINT DEFAULT 0, -- 是否射正
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_match (match_id),
    INDEX idx_match_team (match_id, team)
);

方案2：基于统计字段表（简单场景）

CREATE TABLE match_stats (
    id INT PRIMARY KEY AUTO_INCREMENT,
    match_id INT NOT NULL,
    team VARCHAR(50) NOT NULL,
    shots INT DEFAULT 0,            -- 射门次数
    shots_on_target INT DEFAULT 0,  -- 射正次数
    UNIQUE KEY uk_match_team (match_id, team)
);

统计逻辑（事件表方案）

核心就是 按 team 分组计数,然后比较两队的数量。

<?php
class MatchShotStats
{
    private PDO $pdo;
    public function __construct(PDO $pdo)
    {
        $this->pdo = $pdo;
    }
    /**
     * 统计指定比赛各队的射门次数，并判断哪队更多
     * @return array
     */
    public function compareShots(int $matchId): array
    {
        $sql = "SELECT team, COUNT(*) AS shots
                FROM shots
                WHERE match_id = :match_id
                GROUP BY team
                ORDER BY shots DESC";
        $stmt = $this->pdo->prepare($sql);
        $stmt->execute([':match_id' => $matchId]);
        $rows = $stmt->fetchAll(PDO::FETCH_ASSOC);
        // 构造结果
        $stats = [];
        foreach ($rows as $row) {
            $stats[$row['team']] = (int)$row['shots'];
        }
        // 找出射门更多的队
        $winner = null;
        $maxShots = 0;
        $isDraw = false;
        if (count($stats) >= 2) {
            $values = array_values($stats);
            $maxShots = max($values);
            $teamsWithMax = array_keys($stats, $maxShots, true);
            if (count($teamsWithMax) > 1) {
                $isDraw = true;
            } else {
                $winner = $teamsWithMax[0];
            }
        } elseif (count($stats) === 1) {
            $winner = array_key_first($stats);
            $maxShots = reset($stats);
        }
        return [
            'match_id'   => $matchId,
            'stats'      => $stats,          // ['主队' => 12, '客队' => 8]
            'winner'     => $winner,         // 射门更多的队，或 null
            'max_shots'  => $maxShots,       // 最大射门次数
            'is_draw'    => $isDraw,         // 是否平手
        ];
    }
}

使用示例

$pdo = new PDO('mysql:host=localhost;dbname=football;charset=utf8mb4', 'user', 'pass', [
    PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
]);
$service = new MatchShotStats($pdo);
$result = $service->compareShots(1001);
if ($result['is_draw']) {
    echo "两队射门次数相同，各 {$result['max_shots']} 次\n";
} elseif ($result['winner']) {
    echo "{$result['winner']} 射门更多，共 {$result['max_shots']} 次\n";
} else {
    echo "无射门数据\n";
}
// 输出示例：主队 射门更多，共 12 次

如果只有两队、且想直接返回"谁多"

如果你确定一场比赛只有两队,也可以直接比较这两个值：

public function whoShootsMore(int $matchId, string $teamA, string $teamB): string
{
    $sql = "SELECT team, COUNT(*) AS c
            FROM shots
            WHERE match_id = :mid AND team IN (:a, :b)
            GROUP BY team";
    $stmt = $this->pdo->prepare($sql);
    $stmt->execute([':mid' => $matchId, ':a' => $teamA, ':b' => $teamB]);
    $counts = [$teamA => 0, $teamB => 0];
    foreach ($stmt->fetchAll(PDO::FETCH_ASSOC) as $r) {
        $counts[$r['team']] = (int)$r['c'];
    }
    if ($counts[$teamA] > $counts[$teamB]) return $teamA;
    if ($counts[$teamB] > $counts[$teamA]) return $teamB;
    return '平局';
}

方案2（统计字段表）的 SQL

如果用的是 match_stats 表,一句话搞定：

SELECT team, shots
FROM match_stats
WHERE match_id = :match_id
ORDER BY shots DESC
LIMIT 1;

PHP 里 fetch 后判断是否有第二行、是否相等即可。

性能优化建议

完整可运行 Demo

<?php
// demo.php
$pdo = new PDO('sqlite::memory:');
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$pdo->exec("CREATE TABLE shots (id INTEGER PRIMARY KEY, match_id INT, team TEXT)");
$data = [
    [1, '主队'], [1, '主队'], [1, '主队'],
    [1, '客队'], [1, '客队'],
];
foreach ($data as $i => $d) {
    $pdo->prepare("INSERT INTO shots (match_id, team) VALUES (?, ?)")
        ->execute($d);
}
$stmt = $pdo->query("SELECT team, COUNT(*) AS shots FROM shots WHERE match_id=1 GROUP BY team ORDER BY shots DESC");
$rows = $stmt->fetchAll(PDO::FETCH_ASSOC);
echo "射门统计：\n";
foreach ($rows as $r) {
    echo "  {$r['team']}: {$r['shots']} 次\n";
}
echo "射门更多的队：" . ($rows[0]['shots'] === ($rows[1]['shots'] ?? -1) ? '平局' : $rows[0]['team']) . "\n";

输出：

射门统计：
  主队: 3 次
  客队: 2 次
射门更多的队：主队

如果你能告诉我你的实际表结构（字段名、是事件表还是统计表）和是否只比较两队,我可以把上面的代码改写成直接能贴进你项目的版本。