如何关联多表数据匹配员工与汽车评分并生成报表表格

先梳理现有代码的核心问题：

• 查询可评分员工时未关联当前车辆ID，导致所有车辆共用同一批员工
• 未提前获取指定年月的评分数据，无法匹配员工与评分
• 缺少平均分计算逻辑
• 存在语法错误（如SQL语句末尾缺失分号）
• 未处理评论展示逻辑

以下是修正后的完整实现代码：

<?php
$month = $_POST['month'] ?? 11; // 设默认值避免未传参报错
$year = $_POST['year'] ?? 2022;

// 获取所有车辆数据
$stmt = "SELECT * FROM cars";
$q = $conn->query($stmt);
// 假设使用PDO，若为mysqli则替换为fetch_all(MYSQLI_ASSOC)
$cars = $q->fetchAll(PDO::FETCH_ASSOC); 

// 预存每个车辆的关联数据：可评分员工、对应评分、平均分
$carData = [];
foreach ($cars as $car) {
    $carId = $car['ID'];
    
    // 获取当前车辆的可评分员工列表
    $stmt = "SELECT b.ID as employee_id, b.Name as employee_name 
             FROM employee_can_rate a 
             LEFT JOIN employees b ON a.employeeID = b.ID 
             WHERE a.carID = ?";
    $q = $conn->prepare($stmt);
    $q->execute([$carId]);
    $employees = $q->fetchAll(PDO::FETCH_ASSOC);
    
    // 获取当前车辆指定年月的评分，按员工ID分组存储
    $stmt = "SELECT employeeID, rate, comment 
             FROM car_rates 
             WHERE carID = ? AND month = ? AND year = ?";
    $q = $conn->prepare($stmt);
    $q->execute([$carId, $month, $year]);
    $rates = [];
    foreach ($q->fetchAll(PDO::FETCH_ASSOC) as $rate) {
        $rates[$rate['employeeID']] = $rate;
    }
    
    // 计算当前车辆的平均分
    $totalRate = 0;
    $ratedCount = 0;
    foreach ($rates as $r) {
        $totalRate += $r['rate'];
        $ratedCount++;
    }
    $averageRate = $ratedCount > 0 ? number_format($totalRate / $ratedCount, 1) : 'N/A';
    
    $carData[$carId] = [
        'car' => $car,
        'employees' => $employees,
        'rates' => $rates,
        'averageRate' => $averageRate
    ];
}
?>

<?php foreach ($carData as $data) { 
    $car = $data['car'];
    $employees = $data['employees'];
    $rates = $data['rates'];
    $averageRate = $data['averageRate'];
    $employeeCount = count($employees);
?>
<table class="table table-bordered">
    <thead>
        <tr>
            <th colspan="<?= $employeeCount + 2 ?>">Car <?= $car['Name'] ?></th>  
        </tr>
        <tr>
            <th rowspan="2">Month</th>
            <th colspan="<?= $employeeCount ?>" style="text-align:center">Employees can rate</th> 
            <th rowspan="2">Average rate</th> 
        </tr>
        <tr>
            <?php foreach($employees as $employee): ?>
            <th><?= $employee['employee_name'] ?></th>
            <?php endforeach; ?>
        </tr>
    </thead> 
    <tbody>
        <tr>
            <td rowspan="2"><?= $month .' / '.$year ?></td>
            <?php foreach($employees as $employee): 
                $employeeId = $employee['employee_id'];
                $hasRate = isset($rates[$employeeId]);
            ?>
            <td><?= $hasRate ? $rates[$employeeId]['rate'] : 'No rate' ?></td>
            <?php endforeach; ?>
            <td rowspan="2"><?= $averageRate ?></td>                                                
        </tr>
        <tr>
            <?php foreach($employees as $employee): 
                $employeeId = $employee['employee_id'];
                $hasRate = isset($rates[$employeeId]);
            ?>
            <td><?= $hasRate ? htmlspecialchars($rates[$employeeId]['comment']) : '-' ?></td>
            <?php endforeach; ?>
        </tr>
    </tbody>
</table> 
<?php } ?>

关键改进点说明： #

1. 数据查询优化：

   • 为每个车辆单独查询对应的可评分员工（关联carID），避免数据混乱
   • 预获取指定年月的评分数据并按员工ID分组，后续匹配更高效
   • 使用预处理语句避免SQL注入风险

2. 逻辑完善：

   • 新增平均分计算逻辑，处理无评分时的默认显示
   • 匹配每个员工的评分与评论，无评分时显示统一占位文本
   • 修复原代码中的语法错误，补全缺失的变量定义

3. 前端渲染优化：

   • 将员工评论单独放在一行，符合报表展示逻辑
   • 使用htmlspecialchars转义评论内容，避免XSS风险
   • 动态计算表头colspan属性，适配不同数量的可评分员工

内容的提问来源于stack exchange，提问作者Marko