如何计算MySQL单行q1至q22列平均值并插入查询展示？

一、批量更新现有数据的平均值到ave字段 #

先通过MySQL语句把已存在的每条数据的q1到q22的平均值计算出来并写入ave字段，这样后续查询时直接读取该字段即可：

UPDATE testeval 
SET ave = (
    IFNULL(q1, 0) + IFNULL(q2, 0) + IFNULL(q3, 0) + IFNULL(q4, 0) +
    IFNULL(q5, 0) + IFNULL(q6, 0) + IFNULL(q7, 0) + IFNULL(q8, 0) +
    IFNULL(q9, 0) + IFNULL(q10, 0) + IFNULL(q11, 0) + IFNULL(q12, 0) +
    IFNULL(q13, 0) + IFNULL(q14, 0) + IFNULL(q15, 0) + IFNULL(q16, 0) +
    IFNULL(q17, 0) + IFNULL(q18, 0) + IFNULL(q19, 0) + IFNULL(q20, 0) +
    IFNULL(q21, 0) + IFNULL(q22, 0)
) / 22;

用IFNULL是为了避免某列值为NULL时，整个求和结果变成NULL，确保计算正常。你可以直接在MySQL客户端执行这条语句，或者在PHP中执行一次（执行后记得注释掉，避免每次页面加载重复计算）。

二、修正后的完整PHP代码 #

下面是修复了原代码错误并集成平均值展示的完整代码：

<?php include('includes/header.php'); ?>

<?php 
include_once('../config/dbcon.php'); 

// 仅执行一次批量更新（执行后请注释掉）
// $updateQuery = "UPDATE testeval SET ave = (IFNULL(q1,0)+IFNULL(q2,0)+IFNULL(q3,0)+IFNULL(q4,0)+IFNULL(q5,0)+IFNULL(q6,0)+IFNULL(q7,0)+IFNULL(q8,0)+IFNULL(q9,0)+IFNULL(q10,0)+IFNULL(q11,0)+IFNULL(q12,0)+IFNULL(q13,0)+IFNULL(q14,0)+IFNULL(q15,0)+IFNULL(q16,0)+IFNULL(q17,0)+IFNULL(q18,0)+IFNULL(q19,0)+IFNULL(q20,0)+IFNULL(q21,0)+IFNULL(q22,0))/22";
// mysqli_query($con, $updateQuery);

// 查询数据，包含已计算好的ave字段
$query="select * from testeval"; 
$result=mysqli_query($con,$query); 
?> 
<!DOCTYPE html> 
<html> 

<head>
<style>
table, th, td{
    border: 3px solid black;
    border-collapse: collapse;
}
th{
    padding: 5px;
    text-align: left;
    font-weight: bold;
}
td {
    text-align: center;
}
.title {
        max-width: 100%;
        margin: auto;
}
table {
    text-align: center;
    border: 1px solid black;
    max-width: 1000px;
    line-height: 40px;
}
</style>
</head> 

<body class="title"> 
<table> 
<tr> 
    <th colspan="4"><h2>Evaluation Record</h2></th> 
</tr> 

<?php 
while($row = mysqli_fetch_assoc($result)) 
{ 
?> 
    <tr>
        <th> ID: </th> 
        <td><?php echo $row['id']; ?> </td>
    </tr>
    <tr>
        <th> Name: </th>
        <td><?php echo $row['name']; ?> </td>
    </tr>
    <tr>
        <th> 1. Formulates/adopts objectives of the syllabus course learning outcomes. </th>
        <td><?php echo $row['q1']; ?> </td>
    </tr>
    <tr>
        <th> 2. Selects content and prepares appropriate instructional materials/teaching aids.  </th> 
        <td><?php echo $row['q2']; ?> </td>
    </tr>
    <tr>
        <th> 3. Selects appropriate teaching methods/strategies.  </th> 
        <td><?php echo $row['q3']; ?> </td>
    </tr>
    <tr>
        <th> 4. Relates new lesson with previous knowledge/skills.  </th> 
        <td><?php echo $row['q4']; ?> </td>
    </tr>
    <tr>
        <th> 5. Conveys ideas clearly.  </th> 
        <td><?php echo $row['q5']; ?> </td>
    </tr>
    <tr>
        <th> 6. Utilizes the art of questioning to develop higher level of thinking.  </th> 
        <td><?php echo $row['q6']; ?> </td>
    </tr>
    <tr>
        <th> 7. Ensures students participation. </th> 
        <td><?php echo $row['q7']; ?> </td>
    </tr>
    <tr>
        <th> 8. Shows mastery of the subject matter.  </th> 
        <td><?php echo $row['q8']; ?> </td>
    </tr>
    <tr>
        <th> 9. Utilizes the blackboard or the learning management system of the college.  </th>
        <td><?php echo $row['q9']; ?> </td> <!-- 修复原代码q9取值错误 -->
    </tr>
    <tr>
        <th> 10. Creates assessments that are aligned with the syllabus course learning outcomes  </th> 
        <td><?php echo $row['q10']; ?> </td>
    </tr>
    <tr>
        <th> 11. Evaluates the attainment of the syllabus course learning outcomest.  </th> 
        <td><?php echo $row['q11']; ?> </td>
    </tr>
    <tr>
        <th> 12. Maintains orderly classroom that is conducive to learning.  </th> 
        <td><?php echo $row['q12']; ?> </td>
    </tr>
    <tr>
        <th> 13. Decisiveness  </th> 
        <td><?php echo $row['q13']; ?> </td>
    </tr>
    <tr>
        <th> 14. Honesty / Integrity  </th> 
        <td><?php echo $row['q14']; ?> </td>
    </tr>
    <tr>
        <th> 15. Dedication / Commitment  </th> 
        <td><?php echo $row['q15']; ?> </td>
    </tr>
    <tr>
        <th> 16. Initiative / Resourcefulness  </th> 
        <td><?php echo $row['q16']; ?> </td>
    </tr>
    <tr>
        <th> 17. Courtesy  </th> 
        <td><?php echo $row['q17']; ?> </td>
    </tr>
    <tr>
        <th> 18. Human Relations  </th> 
        <td><?php echo $row['q18']; ?> </td>
    </tr>
    <tr>
        <th> 19. Leadership  </th> 
        <td><?php echo $row['q19']; ?> </td>
    </tr>
    <tr>
        <th> 20. Stress Toleranc  </th> 
        <td><?php echo $row['q20']; ?> </td>
    </tr>
    <tr>
        <th> 21. Fairness / Justice  </th> 
        <td><?php echo $row['q21']; ?> </td>
    </tr>
    <tr>
        <th> 22. Proper Attire / Good Grooming  </th> 
        <td><?php echo $row['q22']; ?> </td>
    </tr>
    <tr>
        <th> Average:   </th> 
        <td><?php echo number_format($row['ave'], 2); ?> </td> <!-- 格式化保留两位小数 -->
    </tr>
    <tr>
        <th> Remarks:  </th> 
        <td><?php echo $row['message']; ?> </td>
    </tr>
    <tr>
        <th colspan="2"><hr style="border:1px solid black;" /></th> 
    </tr>

<?php 
} 
?> 

</table> 
</body> 
</html>

<?php include('includes/footer.php'); ?>

三、额外优化建议 #

• 如果后续有新增或修改q1-q22的需求，可以创建MySQL触发器自动计算ave字段，避免手动更新：

DELIMITER //
-- 插入时自动计算ave
CREATE TRIGGER update_ave_before_insert BEFORE INSERT ON testeval
FOR EACH ROW
BEGIN
    SET NEW.ave = (
        IFNULL(NEW.q1,0)+IFNULL(NEW.q2,0)+IFNULL(NEW.q3,0)+IFNULL(NEW.q4,0)+
        IFNULL(NEW.q5,0)+IFNULL(NEW.q6,0)+IFNULL(NEW.q7,0)+IFNULL(NEW.q8,0)+
        IFNULL(NEW.q9,0)+IFNULL(NEW.q10,0)+IFNULL(NEW.q11,0)+IFNULL(NEW.q12,0)+
        IFNULL(NEW.q13,0)+IFNULL(NEW.q14,0)+IFNULL(NEW.q15,0)+IFNULL(NEW.q16,0)+
        IFNULL(NEW.q17,0)+IFNULL(NEW.q18,0)+IFNULL(NEW.q19,0)+IFNULL(NEW.q20,0)+
        IFNULL(NEW.q21,0)+IFNULL(NEW.q22,0)
    )/22;
END //

-- 更新时自动计算ave
CREATE TRIGGER update_ave_before_update BEFORE UPDATE ON testeval
FOR EACH ROW
BEGIN
    SET NEW.ave = (
        IFNULL(NEW.q1,0)+IFNULL(NEW.q2,0)+IFNULL(NEW.q3,0)+IFNULL(NEW.q4,0)+
        IFNULL(NEW.q5,0)+IFNULL(NEW.q6,0)+IFNULL(NEW.q7,0)+IFNULL(NEW.q8,0)+
        IFNULL(NEW.q9,0)+IFNULL(NEW.q10,0)+IFNULL(NEW.q11,0)+IFNULL(NEW.q12,0)+
        IFNULL(NEW.q13,0)+IFNULL(NEW.q14,0)+IFNULL(NEW.q15,0)+IFNULL(NEW.q16,0)+
        IFNULL(NEW.q17,0)+IFNULL(NEW.q18,0)+IFNULL(NEW.q19,0)+IFNULL(NEW.q20,0)+
        IFNULL(NEW.q21,0)+IFNULL(NEW.q22,0)
    )/22;
END //
DELIMITER ;

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