php项目统计红黄牌数量哪队更多？

wen PHP项目 2026-10-22 1

PHP项目：统计红黄牌数量哪队更多

下面给你一个完整的实现思路和代码示例。

php项目统计红黄牌数量哪队更多？

数据表设计

假设有一张 cards 表记录每张牌：

CREATE TABLE cards (
    id INT AUTO_INCREMENT PRIMARY KEY,
    match_id INT NOT NULL,
    team ENUM('home','away') NOT NULL,   -- 哪一队
    player_name VARCHAR(50),
    card_type ENUM('yellow','red') NOT NULL,
    minute INT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

核心统计逻辑（MySQL 方式）

统计每队黄牌、红牌数量

SELECT
    team,
    SUM(card_type = 'yellow') AS yellow_count,
    SUM(card_type = 'red')    AS red_count,
    COUNT(*)                  AS total_count
FROM cards
WHERE match_id = ?
GROUP BY team;

直接判断哪队红黄牌更多（红牌权重更高）

常见业务里红牌比黄牌"严重"，所以可以加权：红牌算 2 分，黄牌算 1 分。

SELECT
    team,
    SUM(card_type = 'yellow') AS yellow_count,
    SUM(card_type = 'red')    AS red_count,
    SUM(card_type = 'yellow') * 1 + SUM(card_type = 'red') * 2 AS score
FROM cards
WHERE match_id = ?
GROUP BY team
ORDER BY score DESC, red_count DESC, yellow_count DESC;

PHP 实现（PDO 版本）

<?php
function compareCards(PDO $pdo, int $matchId): array
{
    $sql = "SELECT
                team,
                SUM(card_type = 'yellow') AS yellow_count,
                SUM(card_type = 'red')    AS red_count,
                SUM(card_type = 'yellow') * 1 + SUM(card_type = 'red') * 2 AS score
            FROM cards
            WHERE match_id = :match_id
            GROUP BY team";
    $stmt = $pdo->prepare($sql);
    $stmt->execute([':match_id' => $matchId]);
    $rows = $stmt->fetchAll(PDO::FETCH_ASSOC);
    // 初始化，保证两队都存在
    $stats = [
        'home' => ['team' => 'home', 'yellow_count' => 0, 'red_count' => 0, 'score' => 0],
        'away' => ['team' => 'away', 'yellow_count' => 0, 'red_count' => 0, 'score' => 0],
    ];
    foreach ($rows as $row) {
        $stats[$row['team']] = $row;
    }
    $home = $stats['home'];
    $away = $stats['away'];
    // 判断哪队更多
    $moreTeam = 'equal';
    if ($home['score'] > $away['score']) {
        $moreTeam = 'home';
    } elseif ($away['score'] > $home['score']) {
        $moreTeam = 'away';
    }
    return [
        'home'      => $home,
        'away'      => $away,
        'more_team' => $moreTeam,   // home / away / equal
    ];
}
// 使用示例
$pdo = new PDO('mysql:host=localhost;dbname=football;charset=utf8mb4', 'root', 'password');
$result = compareCards($pdo, 1001);
print_r($result);

输出示例

[
    'home' => ['team' => 'home', 'yellow_count' => 3, 'red_count' => 0, 'score' => 3],
    'away' => ['team' => 'away', 'yellow_count' => 2, 'red_count' => 1, 'score' => 4],
    'more_team' => 'away',
]

可以考虑的细节

场景	建议处理
两队牌数相同	`more_team = 'equal'`，前端展示"持平"
红牌权重要不要更高	上面用红=2，黄=1，可按业务调整
是否需要按球员统计	加 `GROUP BY team, player_name`
单场还是赛季汇总	把 `WHERE match_id = ?` 换成 `WHERE season_id = ?`
两黄变一红	业务上通常用直接红牌字段区分，避免把黄牌累计算成红
性能	在 `(match_id, team)` 上加联合索引

只有一张牌一个字段的简化版（card_type 存的是 0/1）

$homeYellow = (int)($row['home_yellow'] ?? 0);
$homeRed    = (int)($row['home_red'] ?? 0);
$awayYellow = (int)($row['away_yellow'] ?? 0);
$awayRed    = (int)($row['away_red'] ?? 0);
$homeScore = $homeYellow + $homeRed * 2;
$awayScore = $awayYellow + $awayRed * 2;
$moreTeam = $homeScore <=> $awayScore; // 正数=home多，负数=away多，0=持平

如果你告诉我：

数据库表结构（字段名）
红黄牌是否要加权（如红牌算 2 分）
是单场还是整个赛季统计

我可以直接给你改成能跑的具体代码。