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

wen PHP项目 2026-11-11 4

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

下面给你一个完整的实现思路和代码示例,涵盖数据库设计、统计逻辑、以及"哪队更多"的判定。

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

数据库表设计

比赛表 `matches`

CREATE TABLE matches (
    id INT PRIMARY KEY AUTO_INCREMENT,
    home_team VARCHAR(50) NOT NULL,   -- 主队
    away_team VARCHAR(50) NOT NULL,   -- 客队
    match_time DATETIME,
    status TINYINT DEFAULT 1          -- 1进行中 2已结束
);

红黄牌记录表 `cards`

CREATE TABLE cards (
    id INT PRIMARY KEY AUTO_INCREMENT,
    match_id INT NOT NULL,
    team VARCHAR(50) NOT NULL,        -- 队伍名
    player VARCHAR(50),               -- 球员
    card_type ENUM('yellow','red') NOT NULL,
    minute INT,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_match (match_id),
    INDEX idx_team (team)
);

💡 建议：用 team_id 关联球队表会更规范，这里为简化用队伍名。

核心统计代码（PDO 示例）

场景1：单场比赛，哪队红黄牌更多？

<?php
function compareCardsInMatch(PDO $pdo, int $matchId): array
{
    $sql = "SELECT team, 
                   SUM(card_type = 'yellow') AS yellow,
                   SUM(card_type = 'red')    AS red,
                   COUNT(*)                  AS total
            FROM cards
            WHERE match_id = :mid
            GROUP BY team";
    $stmt = $pdo->prepare($sql);
    $stmt->execute([':mid' => $matchId]);
    $rows = $stmt->fetchAll(PDO::FETCH_ASSOC);
    if (count($rows) < 2) {
        return ['error' => '该场比赛数据不完整'];
    }
    // 转成以队伍为 key 的数组
    $stat = [];
    foreach ($rows as $r) {
        $stat[$r['team']] = [
            'yellow' => (int)$r['yellow'],
            'red'    => (int)$r['red'],
            'total'  => (int)$r['total'],
        ];
    }
    // 按总牌数排序（红牌可加权，如红=3黄）
    $weighted = [];
    foreach ($stat as $team => $v) {
        $weighted[$team] = $v['yellow'] + $v['red'] * 3; // 可调整权重
    }
    arsort($weighted);
    $teams = array_keys($weighted);
    return [
        'detail'   => $stat,
        'more'     => $weighted[$teams[0]] > $weighted[$teams[1]] ? $teams[0] : '持平',
        'less'     => $weighted[$teams[0]] > $weighted[$teams[1]] ? $teams[1] : '持平',
    ];
}
// 使用
$pdo = new PDO('mysql:host=localhost;dbname=football;charset=utf8mb4', 'root', '');
$result = compareCardsInMatch($pdo, 1);
print_r($result);

输出示例：

Array (
    [detail] => Array (
        [主队A] => Array ( [yellow] => 2 [red] => 1 [total] => 3 )
        [客队B] => Array ( [yellow] => 3 [red] => 0 [total] => 3 )
    )
    [more] => 主队A        // 加权后 A=5, B=3
    [less] => 客队B
)

场景2：统计某队整个赛季的红黄牌总数

function teamSeasonCards(PDO $pdo, string $team, string $from, string $to): array
{
    $sql = "SELECT 
                SUM(c.card_type='yellow') AS yellow,
                SUM(c.card_type='red')    AS red,
                COUNT(*)                  AS total
            FROM cards c
            JOIN matches m ON m.id = c.match_id
            WHERE c.team = :team
              AND m.match_time BETWEEN :from AND :to";
    $stmt = $pdo->prepare($sql);
    $stmt->execute([':team' => $team, ':from' => $from, ':to' => $to]);
    return $stmt->fetch(PDO::FETCH_ASSOC) ?: ['yellow'=>0,'red'=>0,'total'=>0];
}
// 对比两队
$a = teamSeasonCards($pdo, '主队A', '2024-01-01', '2024-12-31');
$b = teamSeasonCards($pdo, '客队B', '2024-01-01', '2024-12-31');
echo "主队A: 黄{$a['yellow']} 红{$a['red']}\n";
echo "客队B: 黄{$b['yellow']} 红{$b['red']}\n";

场景3：球队排行榜（哪队牌最多）

function cardRanking(PDO $pdo, int $limit = 10): array
{
    $sql = "SELECT team,
                   SUM(card_type='yellow') AS yellow,
                   SUM(card_type='red')    AS red,
                   COUNT(*)                AS total,
                   SUM(card_type='yellow') + SUM(card_type='red')*3 AS score
            FROM cards
            GROUP BY team
            ORDER BY score DESC
            LIMIT :lim";
    $stmt = $pdo->prepare($sql);
    $stmt->bindValue(':lim', $limit, PDO::PARAM_INT);
    $stmt->execute();
    return $stmt->fetchAll(PDO::FETCH_ASSOC);
}

"哪队更多"的判定规则建议

红黄牌"更多"不能只比较总数，建议给出明确的判定策略：

策略	说明
简单总数	`total = yellow + red`，直接比大小
加权计分（推荐）	红牌权重更高，如 `score = yellow1 + red3`（FIFA 常见做法）
优先级比较	先比红牌数，红牌多者更"多"；再比黄牌
人均比较	用 `总牌数 / 出场人数`，更公平

示例加权比较封装：

function whoHasMore(array $a, array $b, float $redWeight = 3): string
{
    $sa = $a['yellow'] + $a['red'] * $redWeight;
    $sb = $b['yellow'] + $b['red'] * $redWeight;
    if ($sa == $sb) return '持平';
    return $sa > $sb ? '队伍A更多' : '队伍B更多';
}