本文目录导读：
这是一个关于PHP项目统计脚后跟传球成功次数的技术问题,我来给你一个完整的解决方案。
数据库表结构设计
-- 球员表
CREATE TABLE players (
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(100),
team VARCHAR(50),
position VARCHAR(20)
);
-- 传球记录表
CREATE TABLE passes (
id INT PRIMARY KEY AUTO_INCREMENT,
player_id INT,
pass_type ENUM('heel', 'normal', 'through', 'long') DEFAULT 'normal',
pass_success TINYINT(1) DEFAULT 0, -- 1成功, 0失败
match_id INT,
pass_time DATETIME,
FOREIGN KEY (player_id) REFERENCES players(id)
);
PHP代码实现
基础统计方法
<?php
class HeelPassStatistics {
private $db;
public function __construct($dbConnection) {
$this->db = $dbConnection;
}
/**
* 统计所有球员的脚后跟传球成功次数
* @return array 统计结果数组
*/
public function getAllHeelPassStats() {
$sql = "SELECT
p.id,
p.name,
p.team,
COUNT(CASE WHEN ps.pass_type = 'heel' AND ps.pass_success = 1 THEN 1 END) as heel_success_count,
COUNT(CASE WHEN ps.pass_type = 'heel' THEN 1 END) as total_heel_passes,
ROUND(
(COUNT(CASE WHEN ps.pass_type = 'heel' AND ps.pass_success = 1 THEN 1 END) * 100.0) /
NULLIF(COUNT(CASE WHEN ps.pass_type = 'heel' THEN 1 END), 0),
2
) as success_rate
FROM players p
LEFT JOIN passes ps ON p.id = ps.player_id
GROUP BY p.id, p.name, p.team
ORDER BY heel_success_count DESC";
$result = $this->db->query($sql);
return $result->fetchAll(PDO::FETCH_ASSOC);
}
/**
* 获取单个球员的脚后跟传球统计
* @param int $playerId 球员ID
* @return array 球员统计信息
*/
public function getPlayerHeelPassStats($playerId) {
$sql = "SELECT
p.name,
COUNT(CASE WHEN ps.pass_success = 1 THEN 1 END) as success_count,
COUNT(*) as total_attempts
FROM players p
LEFT JOIN passes ps ON p.id = ps.player_id
WHERE p.id = :playerId AND ps.pass_type = 'heel'
GROUP BY p.name";
$stmt = $this->db->prepare($sql);
$stmt->execute([':playerId' => $playerId]);
return $stmt->fetchAll(PDO::FETCH_ASSOC);
}
/**
* 按比赛统计某球员的脚后跟传球成功率
* @param int $playerId 球员ID
* @param int $matchId 比赛ID
*/
public function getPlayerMatchHeelPassStats($playerId, $matchId) {
$sql = "SELECT
match_id,
SUM(pass_success) as total_success,
COUNT(*) as total_pass,
ROUND((SUM(pass_success) * 100.0) / COUNT(*), 2) as success_rate
FROM passes
WHERE player_id = :playerId
AND pass_type = 'heel'
AND match_id = :matchId
GROUP BY match_id";
$stmt = $this->db->prepare($sql);
$stmt->execute([':playerId' => $playerId, ':matchId' => $matchId]);
return $stmt->fetch(PDO::FETCH_ASSOC);
}
/**
* 按时间范围统计
* @param string $startDate 开始日期
* @param string $endDate 结束日期
*/
public function getHeelPassStatsByDate($startDate, $endDate) {
$sql = "SELECT
DATE(pass_time) as date,
COUNT(CASE WHEN pass_success = 1 THEN 1 END) as success_count,
COUNT(CASE WHEN pass_success = 0 THEN 1 END) as failed_count,
COUNT(*) as total_count
FROM passes
WHERE pass_type = 'heel'
AND pass_time BETWEEN :startDate AND :endDate
GROUP BY DATE(pass_time)
ORDER BY date DESC";
$stmt = $this->db->prepare($sql);
$stmt->execute([':startDate' => $startDate, ':endDate' => $endDate]);
return $stmt->fetchAll(PDO::FETCH_ASSOC);
}
}
简单的统计示例代码
<?php
// 数据库连接
$pdo = new PDO('mysql:host=localhost;dbname=football_db', 'username', 'password');
// 创建统计对象
$stats = new HeelPassStatistics($pdo);
// 1. 获取所有球员的脚后跟传球成功率
$allPlayersStats = $stats->getAllHeelPassStats();
echo "=== 所有球员脚后跟传球统计 ===\n";
foreach ($allPlayersStats as $player) {
echo "{$player['name']} ({$player['team']}): " .
"成功 {$player['heel_success_count']} 次, " .
"成功率 {$player['success_rate']}%\n";
}
// 2. 获取指定球员的统计
$playerId = 1;
$playerStats = $stats->getPlayerHeelPassStats($playerId);
echo "\n=== 球员详细统计 ===\n";
print_r($playerStats);
// 3. 添加一条脚后跟传球记录
function addHeelPass($playerId, $matchId, $success) {
global $pdo;
$sql = "INSERT INTO passes (player_id, pass_type, pass_success, match_id, pass_time)
VALUES (:player, 'heel', :success, :match, NOW())";
$stmt = $pdo->prepare($sql);
$stmt->execute([
':player' => $playerId,
':success' => $success,
':match' => $matchId
]);
return $pdo->lastInsertId();
}
// 4. 更新已有记录
function updatePassResult($passId, $success) {
global $pdo;
$sql = "UPDATE passes SET pass_success = :success WHERE id = :passId";
$stmt = $pdo->prepare($sql);
return $stmt->execute([':success' => $success, ':passId' => $passId]);
}
?>
高级统计功能
<?php
/**
* 高级统计分析类
*/
class AdvancedHeelPassStats extends HeelPassStatistics {
/**
* 球员综合素质分析
*/
public function getPlayerComprehensiveAnalysis($playerId) {
$sql = "SELECT
p.name,
COUNT(DISTINCT ps.match_id) as total_matches,
ROUND(AVG(ps.pass_success) * 100, 2) as avg_success_rate,
SUM(CASE WHEN ps.pass_success = 1 THEN 1 ELSE 0 END) as total_success,
SUM(CASE WHEN ps.pass_success = 0 THEN 1 ELSE 0 END) as total_failed,
SUM(ps.pass_success) * 3 - SUM((1 - ps.pass_success)) as score_rating
FROM players p
LEFT JOIN passes ps ON p.id = ps.player_id
WHERE p.id = :playerId AND ps.pass_type = 'heel'
GROUP BY p.name, p.id";
$stmt = $this->db->prepare($sql);
$stmt->execute([':playerId' => $playerId]);
return $stmt->fetch(PDO::FETCH_ASSOC);
}
/**
* 球队比较分析
*/
public function getTeamComparison() {
$sql = "SELECT
p.team,
COUNT(CASE WHEN ps.pass_success = 1 THEN 1 END) as team_heel_success,
COUNT(CASE WHEN ps.pass_type = 'heel' THEN 1 END) as team_total_heel,
ROUND((SUM(ps.pass_success) * 100.0) / COUNT(ps.id), 2) as team_success_rate
FROM players p
LEFT JOIN passes ps ON p.id = ps.player_id
WHERE ps.pass_type = 'heel'
GROUP BY p.team
ORDER BY team_success_rate DESC";
$result = $this->db->query($sql);
return $result->fetchAll(PDO::FETCH_ASSOC);
}
}
API接口示例
<?php
// API接口设计
header('Content-Type: application/json');
if ($_GET['action'] == 'get_heel_pass_stats') {
$playerId = $_GET['player_id'] ?? null;
if ($playerId) {
$data = $stats->getPlayerHeelPassStats($playerId);
} else {
$data = $stats->getAllHeelPassStats();
}
echo json_encode(['success' => true, 'data' => $data]);
}
// 前端调用示例
?>
<!-- HTML调用示例 -->
<script>
fetch('api.php?action=get_heel_pass_stats')
.then(response => response.json())
.then(data => {
console.log(data);
const tbody = document.getElementById('statsTable');
data.data.forEach(player => {
tbody.innerHTML += `
<tr>
<td>${player.name}</td>
<td>${player.team}</td>
<td>${player.heel_success_count}</td>
<td>${player.success_rate}%</td>
</tr>
`;
});
});
</script>
缓存优化（Redis）
<?php
class CachedHeelPassStats {
private $redis;
private $db;
public function getPlayerStats($playerId) {
$cacheKey = "heel_pass_stats_player_{$playerId}";
// 尝试从缓存获取
$cached = $this->redis->get($cacheKey);
if ($cached) {
return json_decode($cached, true);
}
// 查询数据库
$data = $this->db->getPlayerHeelPassStats($playerId);
// 存入缓存，有效期10分钟
$this->redis->setex($cacheKey, 600, json_encode($data));
return $data;
}
public function updatePlayerStats($playerId) {
$cacheKey = "heel_pass_stats_player_{$playerId}";
$this->redis->del($cacheKey);
}
}
?>
使用建议
- 数据完整性：确保
pass_success字段严格使用0/1值 - 性能优化：为
pass_type和pass_success字段创建索引 - 实时性：根据需求决定采用实时统计还是缓存统计
- 扩展性：可以扩展其他传球的统计数据
这个方案完整覆盖了脚后跟传球统计的所有需求,可以根据实际项目规模进行调整。
