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

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

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

数据表设计

假设有两张表：matches（比赛）和 cards（红黄牌记录）。

-- 比赛表
CREATE TABLE matches (
    id INT PRIMARY KEY AUTO_INCREMENT,
    home_team VARCHAR(50),
    away_team VARCHAR(50),
    match_time DATETIME
);
-- 红黄牌表
CREATE TABLE cards (
    id INT PRIMARY KEY AUTO_INCREMENT,
    match_id INT,
    team VARCHAR(50),        -- 球队名
    player_name VARCHAR(50),
    card_type ENUM('yellow','red'),
    minute INT
);

核心统计 SQL

按球队统计红黄牌总数

SELECT 
    team,
    SUM(CASE WHEN card_type = 'yellow' THEN 1 ELSE 0 END) AS yellow_count,
    SUM(CASE WHEN card_type = 'red'    THEN 1 ELSE 0 END) AS red_count,
    COUNT(*) AS total
FROM cards
WHERE match_id = ?
GROUP BY team;

判断哪队牌更多

SELECT 
    team,
    SUM(CASE WHEN card_type = 'yellow' THEN 1 ELSE 0 END) AS yellow_count,
    SUM(CASE WHEN card_type = 'red'    THEN 1 ELSE 0 END) AS red_count,
    -- 红牌权重更高（可选）
    SUM(CASE WHEN card_type = 'yellow' THEN 1 ELSE 2 END) AS score
FROM cards
WHERE match_id = ?
GROUP BY team
ORDER BY score DESC
LIMIT 1;

PHP 完整实现

<?php
class CardStatistics
{
    private PDO $pdo;
    public function __construct(PDO $pdo)
    {
        $this->pdo = $pdo;
    }
    /**
     * 统计一场比赛各队的红黄牌
     */
    public function getMatchCards(int $matchId): array
    {
        $sql = "SELECT 
                    team,
                    SUM(card_type = 'yellow') AS yellow_count,
                    SUM(card_type = 'red')    AS red_count,
                    COUNT(*)                  AS total,
                    SUM(CASE WHEN card_type = 'yellow' THEN 1 ELSE 2 END) AS score
                FROM cards
                WHERE match_id = :match_id
                GROUP BY team";
        $stmt = $this->pdo->prepare($sql);
        $stmt->execute([':match_id' => $matchId]);
        return $stmt->fetchAll(PDO::FETCH_ASSOC);
    }
    /**
     * 判断哪队红黄牌更多
     * @return array ['more_team' => 'xx队', 'is_tie' => bool, 'detail' => [...]]
     */
    public function compareTeams(int $matchId): array
    {
        $stats = $this->getMatchCards($matchId);
        if (count($stats) < 2) {
            return [
                'more_team' => $stats[0]['team'] ?? '无数据',
                'is_tie'    => false,
                'detail'    => $stats,
            ];
        }
        // 按 score 降序排序
        usort($stats, fn($a, $b) => $b['score'] <=> $a['score']);
        $first  = $stats[0];
        $second = $stats[1];
        // 判断是否打平
        if ((int)$first['score'] === (int)$second['score']
            && (int)$first['total'] === (int)$second['total']) {
            return [
                'more_team' => null,
                'is_tie'    => true,
                'detail'    => $stats,
            ];
        }
        return [
            'more_team' => $first['team'],
            'is_tie'    => false,
            'detail'    => $stats,
        ];
    }
}
// ============ 使用示例 ============
$pdo = new PDO('mysql:host=localhost;dbname=football;charset=utf8mb4', 'root', 'password');
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$service = new CardStatistics($pdo);
$result  = $service->compareTeams(1001);
if ($result['is_tie']) {
    echo "两队红黄牌数量持平\n";
} else {
    echo "牌更多的球队是：{$result['more_team']}\n";
}
// 打印详细数据
foreach ($result['detail'] as $row) {
    echo "{$row['team']} - 黄牌:{$row['yellow_count']} 红牌:{$row['red_count']} 总计:{$row['total']}\n";
}

输出示例

牌更多的球队是：阿根廷
阿根廷 - 黄牌:3 红牌:1 总计:4
巴西   - 黄牌:2 红牌:0 总计:2

关键点说明

如果你需要按整个赛事/赛季统计（不只是单场），把 WHERE match_id = ? 换成 JOIN matches 并加时间范围条件即可,需要我补充这部分吗？