如何使用 PHP 和 MySQL 查询并展示每头牲畜的三次体重变化及增长评估-php教程-PHP中文网

如何使用 PHP 和 MySQL 查询并展示每头牲畜的三次体重变化及增长评估

本文介绍如何通过 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 行），并通过条件聚合提取前三次体重及对应日期：

btpanel phpsite 宝塔面板PHP网站

宝塔面板 PHP 网站管理：站点创建、删除、启停、PHP 版本切换、域名管理、SSL证书管理、伪静态管理、数据库管理

下载

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  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);
?>



<title>Cattle Weight Trend Report</title>

Cattle ID	Last Weight	2nd Last Weight	3rd Last Weight	Remarks	Last Weight Date
= htmlspecialchars($row['Cattle ID']) ?>	= htmlspecialchars($row['Last Weight']) ?>	= htmlspecialchars($row['2nd Last Weight']) ?>	= htmlspecialchars($row['3rd Last Weight']) ?>	= htmlspecialchars($row['Remarks']) ?>	= htmlspecialchars($row['Last Weight Date']) ?>