本文介绍如何通过 mysql 窗口函数与 php 后端逻辑，从体重记录表中按 cattle_id 聚合提取最近三次体重、计算增长幅度，并依据每日 ≥1kg 增长标准自动标注“satisfactory”或“not satisfactory”，最终以结构化 html 表格呈现。
本文介绍如何通过 mysql 窗口函数与 php 后端逻辑，从体重记录表中按 cattle_id 聚合提取最近三次体重、计算增长幅度，并依据每日 ≥1kg 增长标准自动标注“satisfactory”或“not satisfactory”，最终以结构化 html 表格呈现。
要实现按牲畜 ID 拉取最近三次体重记录、计算增长趋势并生成带评估备注的表格，需结合 MySQL 8.0+ 的窗口函数能力与 PHP 的数据库交互和前端渲染能力。核心难点在于：同一 cattle_id 多条记录需按时间倒序排列，取最新三条，并准确计算时间跨度内的总增长量（用于判断日均增长是否达标）。
✅ 推荐 SQL 查询（MySQL 8.0+）
以下优化后的查询使用 ROW_NUMBER() 按 weight_date 降序编号（确保“最新”为第 1 行），并通过条件聚合提取前三次体重及对应日期：
WITH ranked_weights AS (
SELECT
cattle_id,
weight,
weight_date,
ROW_NUMBER() OVER (PARTITION BY cattle_id ORDER BY weight_date DESC) AS rn
FROM weight_calc
),
pivoted AS (
SELECT
cattle_id,
MAX(CASE WHEN rn = 1 THEN weight END) AS last_weight,
MAX(CASE WHEN rn = 2 THEN weight END) AS second_last_weight,
MAX(CASE WHEN rn = 3 THEN weight END) AS third_last_weight,
MAX(CASE WHEN rn = 1 THEN weight_date END) AS last_weight_date,
-- 提取对应日期用于天数计算（假设格式为 'dd-mm-yyyy'，需转为 DATE）
STR_TO_DATE(MAX(CASE WHEN rn = 1 THEN weight_date END), '%d-%m-%Y') AS date1,
STR_TO_DATE(MAX(CASE WHEN rn = 3 THEN weight_date END), '%d-%m-%Y') AS date3
FROM ranked_weights
WHERE rn <= 3
GROUP BY cattle_id
)
SELECT
cattle_id AS "Cattle ID",
COALESCE(last_weight, 'N/A') AS "Last Weight",
COALESCE(second_last_weight, 'N/A') AS "2nd Last Weight",
COALESCE(third_last_weight, 'N/A') AS "3rd Last Weight",
CASE
WHEN date1 IS NOT NULL AND date3 IS NOT NULL
AND DATEDIFF(date1, date3) > 0
AND (last_weight - third_last_weight) >= DATEDIFF(date1, date3)
THEN 'Satisfactory'
ELSE 'Not Satisfactory'
END AS "Remarks",
DATE_FORMAT(last_weight_date, '%d-%m-%Y') AS "Last Weight Date"
FROM pivoted
ORDER BY cattle_id;⚠️ 注意事项：
- weight_date 字段必须为 DATE 类型或可被 STR_TO_DATE() 正确解析（如 '30-01-2022' → %d-%m-%Y）；若为 VARCHAR，强烈建议迁移至 DATE 类型并用 YYYY-MM-DD 格式存储。
- DATEDIFF(date1, date3) 返回整数天数，last_weight - third_last_weight 单位为 KG，因此“≥1 kg/天”等价于 增长总量 ≥ 总天数。
- 使用 COALESCE() 防止某牲畜记录不足 3 条时出现 NULL，提升表格健壮性。
✅ PHP 后端实现示例
<?php
$host = 'localhost'; $db = 'farm_db'; $user = 'root'; $pass = '';
$pdo = new PDO("mysql:host=$host;dbname=$db;charset=utf8mb4", $user, $pass);
$sql = "/* 上述完整SQL */";
$stmt = $pdo->query($sql);
$results = $stmt->fetchAll(PDO::FETCH_ASSOC);
?>
<!DOCTYPE html>
<html>
<head><title>Cattle Weight Trend Report</title></head>
<body>
<table border="1" class="s-table">
<thead>
<tr>
<th>Cattle ID</th>
<th>Last Weight</th>
<th>2nd Last Weight</th>
<th>3rd Last Weight</th>
<th>Remarks</th>
<th>Last Weight Date</th>
</tr>
</thead>
<tbody>
<?php foreach ($results as $row): ?>
<tr>
<td><?= htmlspecialchars($row['Cattle ID']) ?></td>
<td><?= htmlspecialchars($row['Last Weight']) ?></td>
<td><?= htmlspecialchars($row['2nd Last Weight']) ?></td>
<td><?= htmlspecialchars($row['3rd Last Weight']) ?></td>
<td style="color:<?= $row['Remarks']==='Satisfactory' ? 'green' : 'red' ?>">
<?= htmlspecialchars($row['Remarks']) ?>
</td>
<td><?= htmlspecialchars($row['Last Weight Date']) ?></td>
</tr>
<?php endforeach; ?>
</tbody>
</table>
</body>
</html>✅ 关键总结
- 数据库层：依赖 MySQL 8.0+ 窗口函数完成时间排序与行号标记，避免复杂嵌套子查询；
- 业务逻辑层：增长评估应基于 DATEDIFF() 计算真实天数，而非硬编码“15天×2=30天”，以兼容可能的测量延迟或提前；
- 安全与可维护性：PHP 中务必使用 PDO 预处理机制（本例因无用户输入可简化），输出前用 htmlspecialchars() 防 XSS；
- 扩展建议：如需支持动态 N 次记录或自定义评估规则，可将 SQL 封装为存储过程，参数化 @n 和 @min_daily_gain。
该方案兼顾准确性、可读性与生产可用性，适用于畜牧管理、养殖数字化等实际场景。
立即学习“PHP免费学习笔记（深入）”；
