php项目统计角球数哪边领先？

PHP项目统计角球数哪边领先

下面给你一个完整的 PHP 实现方案，用于统计足球比赛中角球数哪边领先。

数据表设计

CREATE TABLE match_corners (
    id INT AUTO_INCREMENT PRIMARY KEY,
    match_id INT NOT NULL,
    team ENUM('home','away') NOT NULL,  -- 主队/客队
    corner_time INT NOT NULL,            -- 比赛分钟
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

核心统计函数

<?php
/**
 * 统计比赛中角球领先方
 * @param PDO $pdo
 * @param int $matchId 比赛ID
 * @return array
 */
function getCornerStats(PDO $pdo, int $matchId): array
{
    $sql = "SELECT team, COUNT(*) AS total
            FROM match_corners
            WHERE match_id = :mid
            GROUP BY team";
    $stmt = $pdo->prepare($sql);
    $stmt->execute([':mid' => $matchId]);
    $rows = $stmt->fetchAll(PDO::FETCH_KEY_PAIR); // ['home'=>x, 'away'=>y]
    $home = (int)($rows['home'] ?? 0);
    $away = (int)($rows['away'] ?? 0);
    // 判断领先方
    if ($home > $away) {
        $leader = 'home';
        $diff   = $home - $away;
    } elseif ($away > $home) {
        $leader = 'away';
        $diff   = $away - $home;
    } else {
        $leader = 'tie';
        $diff   = 0;
    }
    return [
        'match_id' => $matchId,
        'home'     => $home,
        'away'     => $away,
        'leader'   => $leader,     // home | away | tie
        'diff'     => $diff,       // 领先角球数
        'total'    => $home + $away,
        'desc'     => buildDesc($home, $away, $leader, $diff),
    ];
}
function buildDesc(int $home, int $away, string $leader, int $diff): string
{
    if ($leader === 'tie') {
        return "双方角球持平（{$home} : {$away}）";
    }
    $teamName = $leader === 'home' ? '主队' : '客队';
    return "{$teamName}领先 {$diff} 个角球（{$home} : {$away}）";
}

使用示例

$pdo = new PDO('mysql:host=127.0.0.1;dbname=football;charset=utf8mb4', 'root', '');
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$result = getCornerStats($pdo, 1001);
print_r($result);
// 输出示例：
// [
//   'match_id' => 1001,
//   'home' => 5,
//   'away' => 3,
//   'leader' => 'home',
//   'diff' => 2,
//   'total' => 8,
//   'desc' => '主队领先 2 个角球（5 : 3）'
// ]

进阶：实时走势（哪边先领先/反超）

如果你想统计"角球数走势变化"，即哪边先领先、何时被反超：

function getCornerTrend(PDO $pdo, int $matchId): array
{
    $stmt = $pdo->prepare(
        "SELECT team, corner_time FROM match_corners
         WHERE match_id = :mid ORDER BY corner_time ASC, id ASC"
    );
    $stmt->execute([':mid' => $matchId]);
    $home = $away = 0;
    $trend = [];
    foreach ($stmt as $row) {
        $row['team'] === 'home' ? $home++ : $away++;
        $leader = $home <=> $away; // 1 home, -1 away, 0 tie
        $trend[] = [
            'time'   => $row['corner_time'],
            'team'   => $row['team'],
            'home'   => $home,
            'away'   => $away,
            'leader' => $leader,
        ];
    }
    return $trend;
}

常用 SQL 快捷查询

一条 SQL 直接返回领先方：

SELECT
    SUM(team='home') AS home_corners,
    SUM(team='away') AS away_corners,
    CASE
        WHEN SUM(team='home') > SUM(team='away') THEN 'home'
        WHEN SUM(team='home') < SUM(team='away') THEN 'away'
        ELSE 'tie'
    END AS leader
FROM match_corners
WHERE match_id = 1001;

批量统计多场比赛：

SELECT match_id,
       SUM(team='home') AS home_c,
       SUM(team='away') AS away_c,
       ABS(SUM(team='home') - SUM(team='away')) AS diff
FROM match_corners
GROUP BY match_id
HAVING home_c <> away_c
ORDER BY diff DESC;

注意事项

1. 数据准确性：角球数据需录入准确，可加唯一约束防止重复录入（同一 match_id + team + corner_time）。
2. 缓存优化：高频查询建议用 Redis 缓存角球数，比赛结束时再落库。
3. 接口设计：对外 API 返回 leader 字段用枚举值（home/away/tie），便于前端判断。
4. 并发：实时录入建议用事务 + 行锁或消息队列异步处理。

如果你需要接入第三方数据 API（如足球数据接口）、WebSocket 实时推送或前端展示页面，告诉我具体场景，我可以继续补充。