php项目统计犯规战术阻止反击几次？

wen PHP项目 2026-10-05 4

足球项目中统计"犯规战术阻止反击"次数的实现方案

先明确业务定义

"犯规战术阻止反击"（Tactical Foul to Stop Counter-Attack） 通常需要满足以下条件：

php项目统计犯规战术阻止反击几次？

条件	说明
对方处于反击状态	由守转攻，且有明显人数/空间优势
本方犯规	抢断失败、拉拽、铲球等被判犯规
犯规目的	阻断对方快攻，而非正常对抗
位置	通常在中前场或危险区域

⚠️ 实际项目中，"是否反击"这种主观判断很难纯自动判定，建议采用"事件标记 + 人工/规则辅助"的方式。

数据库设计

-- 比赛表
CREATE TABLE matches (
    id INT PRIMARY KEY AUTO_INCREMENT,
    home_team VARCHAR(50),
    away_team VARCHAR(50),
    match_time DATETIME
);
-- 犯规事件表（核心）
CREATE TABLE foul_events (
    id INT PRIMARY KEY AUTO_INCREMENT,
    match_id INT NOT NULL,
    team_id INT NOT NULL,              -- 犯规方
    player_id INT NOT NULL,
    minute INT NOT NULL,
    half TINYINT NOT NULL,             -- 1 上半场 2 下半场
    x_coord DECIMAL(5,2),              -- 场地坐标
    y_coord DECIMAL(5,2),
    foul_type VARCHAR(30),             -- 拉拽/铲球/推人等
    is_counter_attack TINYINT DEFAULT 0,      -- 是否处于对方反击中
    is_tactical_foul TINYINT DEFAULT 0,       -- 是否战术犯规
    is_stop_counter TINYINT DEFAULT 0,        -- ★ 是否"阻止反击"（最终标记）
    card_type TINYINT DEFAULT 0,       -- 0无 1黄 2红
    remark VARCHAR(255),
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_match_team (match_id, team_id)
);

关键点：is_stop_counter 是最终判定字段，可由人工标记或规则自动计算。

核心统计 SQL

统计某队在某场比赛的"阻止反击犯规"次数

SELECT 
    m.id AS match_id,
    m.home_team,
    m.away_team,
    t.team_name,
    COUNT(*) AS stop_counter_count
FROM foul_events fe
JOIN matches m ON fe.match_id = m.id
JOIN teams t ON fe.team_id = t.id
WHERE fe.match_id = ?
  AND fe.team_id = ?
  AND fe.is_stop_counter = 1
GROUP BY m.id, t.team_name;

统计某队整个赛季的"阻止反击犯规"次数

SELECT 
    t.team_name,
    COUNT(*) AS total,
    SUM(fe.card_type = 1) AS yellow_cards,
    SUM(fe.card_type = 2) AS red_cards
FROM foul_events fe
JOIN teams t ON fe.team_id = t.id
WHERE fe.is_stop_counter = 1
GROUP BY fe.team_id
ORDER BY total DESC;

按球员排行（"战术犯规王"）

SELECT 
    p.player_name,
    t.team_name,
    COUNT(*) AS stop_counter_count
FROM foul_events fe
JOIN players p ON fe.player_id = p.id
JOIN teams t ON fe.team_id = t.id
WHERE fe.is_stop_counter = 1
GROUP BY fe.player_id
ORDER BY stop_counter_count DESC
LIMIT 20;

自动判定的规则引擎（可选）

如果不想全人工标记,可写一个判定函数/定时任务：

<?php
/**
 * 自动判定是否为"阻止反击的战术犯规"
 * 需配合传球/控球事件表使用
 */
function judgeStopCounterFoul(array $foul, array $lastEvents): bool
{
    // 规则1：对方在犯规前 N 秒内由守转攻
    if (!$lastEvents['is_counter_attack']) {
        return false;
    }
    // 规则2：犯规位置在中前场（离对方球门较近）
    if ($foul['x_coord'] < 40) {  // 假设 0-100 坐标系
        return false;
    }
    // 规则3：犯规类型为拉拽/铲球/战术性阻挡
    $tacticalTypes = ['拉拽', '铲球', '推人', '阻挡'];
    if (!in_array($foul['foul_type'], $tacticalTypes)) {
        return false;
    }
    // 规则4：反击方有进攻人数优势（需接入人数数据）
    if (($lastEvents['attackers'] ?? 0) <= ($lastEvents['defenders'] ?? 99)) {
        return false;
    }
    return true;
}

PHP 查询封装示例

<?php
class FoulStatsService
{
    public function __construct(private PDO $pdo) {}
    /**
     * 获取某场比赛两队的"阻止反击犯规"次数
     */
    public function getStopCounterByMatch(int $matchId): array
    {
        $sql = "SELECT 
                    t.id AS team_id,
                    t.team_name,
                    COUNT(fe.id) AS stop_counter_count
                FROM teams t
                LEFT JOIN foul_events fe 
                    ON fe.team_id = t.id 
                    AND fe.match_id = :mid 
                    AND fe.is_stop_counter = 1
                WHERE t.id IN (
                    SELECT home_team_id FROM matches WHERE id = :mid
                    UNION
                    SELECT away_team_id FROM matches WHERE id = :mid
                )
                GROUP BY t.id, t.team_name";
        $stmt = $this->pdo->prepare($sql);
        $stmt->execute([':mid' => $matchId]);
        return $stmt->fetchAll(PDO::FETCH_ASSOC);
    }
    /**
     * 获取某队赛季总览
     */
    public function getTeamSeasonSummary(int $teamId): array
    {
        $sql = "SELECT 
                    COUNT(*) AS total,
                    SUM(card_type = 1) AS yellow,
                    SUM(card_type = 2) AS red
                FROM foul_events
                WHERE team_id = :tid AND is_stop_counter = 1";
        $stmt = $this->pdo->prepare($sql);
        $stmt->execute([':tid' => $teamId]);
        return $stmt->fetch(PDO::FETCH_ASSOC) ?: [];
    }
}

调用：

$pdo = new PDO('mysql:host=localhost;dbname=football;charset=utf8mb4', 'root', '');
$svc = new FoulStatsService($pdo);
print_r($svc->getStopCounterByMatch(1001));
// 输出：
// [
//   ['team_id'=>1, 'team_name'=>'A队', 'stop_counter_count'=>3],
//   ['team_id'=>2, 'team_name'=>'B队', 'stop_counter_count'=>1],
// ]