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

本文目录导读：

1. 数据库表结构设计
2. PHP核心统计类
3. 使用示例
4. 前端展示示例（HTML）
5. 优化建议

我来为您设计一个PHP项目统计红黄牌数量的解决方案，假设这是一个足球赛事管理系统,我会提供完整的代码实现。

数据库表结构设计

-- 球队表
CREATE TABLE teams (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL,
    short_name VARCHAR(50)
);
-- 球员表
CREATE TABLE players (
    id INT PRIMARY KEY AUTO_INCREMENT,
    team_id INT NOT NULL,
    name VARCHAR(100) NOT NULL,
    number INT,
    FOREIGN KEY (team_id) REFERENCES teams(id)
);
-- 比赛表
CREATE TABLE matches (
    id INT PRIMARY KEY AUTO_INCREMENT,
    home_team_id INT NOT NULL,
    away_team_id INT NOT NULL,
    match_date DATE,
    FOREIGN KEY (home_team_id) REFERENCES teams(id),
    FOREIGN KEY (away_team_id) REFERENCES teams(id)
);
-- 红黄牌记录表
CREATE TABLE cards (
    id INT PRIMARY KEY AUTO_INCREMENT,
    match_id INT NOT NULL,
    player_id INT NOT NULL,
    card_type ENUM('red', 'yellow') NOT NULL,
    card_time INT COMMENT '比赛时间(分钟)',
    FOREIGN KEY (match_id) REFERENCES matches(id),
    FOREIGN KEY (player_id) REFERENCES players(id)
);

PHP核心统计类

<?php
class CardStatistics {
    private $pdo;
    public function __construct($pdo) {
        $this->pdo = $pdo;
    }
    /**
     * 获取所有球队红黄牌统计
     */
    public function getAllTeamsCardStats() {
        $sql = "
            SELECT 
                t.id as team_id,
                t.name as team_name,
                COUNT(CASE WHEN c.card_type = 'red' THEN 1 END) as red_cards,
                COUNT(CASE WHEN c.card_type = 'yellow' THEN 1 END) as yellow_cards,
                (COUNT(CASE WHEN c.card_type = 'red' THEN 1 END) * 2 + 
                 COUNT(CASE WHEN c.card_type = 'yellow' THEN 1 END)) as total_points
            FROM teams t
            LEFT JOIN players p ON p.team_id = t.id
            LEFT JOIN cards c ON c.player_id = p.id
            GROUP BY t.id, t.name
            ORDER BY total_points DESC, red_cards DESC
        ";
        return $this->pdo->query($sql)->fetchAll(PDO::FETCH_ASSOC);
    }
    /**
     * 获取红黄牌最多的球队
     */
    public function getTopCardTeams($limit = 5) {
        $sql = "
            SELECT 
                t.name as team_name,
                COUNT(CASE WHEN c.card_type = 'red' THEN 1 END) as red_cards,
                COUNT(CASE WHEN c.card_type = 'yellow' THEN 1 END) as yellow_cards,
                (COUNT(CASE WHEN c.card_type = 'red' THEN 1 END) * 2 + 
                 COUNT(CASE WHEN c.card_type = 'yellow' THEN 1 END)) as total_points
            FROM teams t
            LEFT JOIN players p ON p.team_id = t.id
            LEFT JOIN cards c ON c.player_id = p.id
            GROUP BY t.id, t.name
            ORDER BY total_points DESC
            LIMIT :limit
        ";
        $stmt = $this->pdo->prepare($sql);
        $stmt->bindValue(':limit', $limit, PDO::PARAM_INT);
        $stmt->execute();
        return $stmt->fetchAll(PDO::FETCH_ASSOC);
    }
    /**
     * 比较两个球队的红黄牌数量
     */
    public function compareTwoTeams($team1_id, $team2_id) {
        $sql = "
            SELECT 
                t.id,
                t.name,
                COUNT(CASE WHEN c.card_type = 'red' THEN 1 END) as red_cards,
                COUNT(CASE WHEN c.card_type = 'yellow' THEN 1 END) as yellow_cards
            FROM teams t
            LEFT JOIN players p ON p.team_id = t.id
            LEFT JOIN cards c ON c.player_id = p.id
            WHERE t.id IN (:team1, :team2)
            GROUP BY t.id, t.name
        ";
        $stmt = $this->pdo->prepare($sql);
        $stmt->bindValue(':team1', $team1_id, PDO::PARAM_INT);
        $stmt->bindValue(':team2', $team2_id, PDO::PARAM_INT);
        $stmt->execute();
        $teams = $stmt->fetchAll(PDO::FETCH_ASSOC);
        // 判断哪队更多
        $team1 = $teams[0] ?? null;
        $team2 = $teams[1] ?? null;
        if ($team1 && $team2) {
            $team1_score = $team1['red_cards'] * 2 + $team1['yellow_cards'];
            $team2_score = $team2['red_cards'] * 2 + $team2['yellow_cards'];
            if ($team1_score > $team2_score) {
                return "{$team1['name']} 的红黄牌更多（红牌:{$team1['red_cards']},黄牌:{$team1['yellow_cards']}）";
            } elseif ($team2_score > $team1_score) {
                return "{$team2['name']} 的红黄牌更多（红牌:{$team2['red_cards']},黄牌:{$team2['yellow_cards']}）";
            } else {
                return "两队红黄牌数量相同";
            }
        }
        return "无法比较";
    }
    /**
     * 获取某场比赛的红黄牌统计
     */
    public function getMatchCardStats($match_id) {
        $sql = "
            SELECT 
                t.name as team_name,
                COUNT(CASE WHEN c.card_type = 'red' THEN 1 END) as red_cards,
                COUNT(CASE WHEN c.card_type = 'yellow' THEN 1 END) as yellow_cards
            FROM matches m
            JOIN teams t ON t.id IN (m.home_team_id, m.away_team_id)
            LEFT JOIN players p ON p.team_id = t.id
            LEFT JOIN cards c ON c.player_id = p.id AND c.match_id = m.id
            WHERE m.id = :match_id
            GROUP BY t.id, t.name
        ";
        $stmt = $this->pdo->prepare($sql);
        $stmt->bindValue(':match_id', $match_id, PDO::PARAM_INT);
        $stmt->execute();
        return $stmt->fetchAll(PDO::FETCH_ASSOC);
    }
}

使用示例

<?php
// 数据库连接配置
$dsn = 'mysql:host=localhost;dbname=football';
$username = 'root';
$password = 'password';
try {
    $pdo = new PDO($dsn, $username, $password);
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    $stats = new CardStatistics($pdo);
    // 1. 获取所有球队统计
    echo "=== 所有球队红黄牌统计 ===\n";
    $allTeams = $stats->getAllTeamsCardStats();
    foreach ($allTeams as $team) {
        echo "{$team['team_name']}: 红牌={$team['red_cards']}, 黄牌={$team['yellow_cards']}, 积分={$team['total_points']}\n";
    }
    // 2. 获取红黄牌最多的5支球队
    echo "\n=== 红黄牌最多球队TOP5 ===\n";
    $topTeams = $stats->getTopCardTeams(5);
    foreach ($topTeams as $index => $team) {
        echo ($index + 1) . ". {$team['team_name']}: 红牌={$team['red_cards']}, 黄牌={$team['yellow_cards']}\n";
    }
    // 3. 比较两支球队
    echo "\n=== 球队比较 ===\n";
    echo $stats->compareTwoTeams(1, 2) . "\n";
    // 4. 查看某场比赛统计
    echo "\n=== 某场比赛统计 ===\n";
    $matchStats = $stats->getMatchCardStats(1);
    foreach ($matchStats as $team) {
        echo "{$team['team_name']}: 红牌={$team['red_cards']}, 黄牌={$team['yellow_cards']}\n";
    }
} catch (PDOException $e) {
    echo "数据库连接失败: " . $e->getMessage();
}

前端展示示例（HTML）

<!DOCTYPE html>
<html>
<head>球队红黄牌统计</title>
    <style>
        .card-stats { width: 80%; margin: 20px auto; }
        table { width: 100%; border-collapse: collapse; }
        th, td { padding: 10px; border: 1px solid #ddd; text-align: center; }
        th { background-color: #4CAF50; color: white; }
        .red-card { color: red; font-weight: bold; }
        .yellow-card { color: #FFD700; font-weight: bold; }
    </style>
</head>
<body>
    <div class="card-stats">
        <h2>球队红黄牌排行榜</h2>
        <table>
            <thead>
                <tr>
                    <th>排名</th>
                    <th>球队名称</th>
                    <th>红牌</th>
                    <th>黄牌</th>
                    <th>总积分</th>
                </tr>
            </thead>
            <tbody>
                <?php
                // 这里调用你的统计方法
                $allTeams = $stats->getAllTeamsCardStats();
                foreach ($allTeams as $index => $team) { ?>
                    <tr>
                        <td><?php echo $index + 1; ?></td>
                        <td><?php echo htmlspecialchars($team['team_name']); ?></td>
                        <td class="red-card"><?php echo $team['red_cards']; ?></td>
                        <td class="yellow-card"><?php echo $team['yellow_cards']; ?></td>
                        <td><?php echo $team['total_points']; ?></td>
                    </tr>
                <?php } ?>
            </tbody>
        </table>
    </div>
</body>
</html>

优化建议

1. 添加索引：在cards表的player_id和match_id上添加索引
2. 缓存处理：对于频繁查询的统计结果，可以使用Redis等缓存
3. 分页显示：如果球队数量多，建议添加分页功能
4. 数据验证：添加输入数据验证，防止SQL注入

这个解决方案可以灵活扩展，支持多种统计需求和展示方式,您可以根据实际业务需求进行调整和优化。