MySQL8中查询指定Bird的最新检查及指标平均值的SQL实现

环境与表结构 #

环境：MySQL 8
现有4张表：bird、check、weights、measurements，表结构及数据如下：

bird表 #

+----+--------+--------+
| id |  name  |  sex   |
+----+--------+--------+
| 1  | bob    | male   |
| 2  | mary   | female |
| 3  | jono   | male   |
+----+--------+--------+ 

check表（与bird表通过bird_id关联） #

+----+-------------+---------+
| id | check_date  | bird_id |
+----+-------------+---------+
| 1  | 2023-06-01  |       2 |
| 2  | 2023-07-01  |       2 |
| 3  | 2023-08-01  |       2 |
+----+-------------+---------+

weights表（每个check可关联多条记录，通过check_id关联） #

+----+---------+----------+
| id | weight  | check_id | 
+----+---------+----------+
|  1 |    3.3  |        2 |
|  2 |    3.45 |        2 |
|  3 |    3.8  |        2 |
|  4 |    4.5  |        1 |
|  5 |    4.51 |        1 |
+----+---------+----------+

measurements表（每个check可关联多条记录，通过check_id关联） #

+----+---------+----------+
| id | measur  | check_id |
+----+---------+----------+
|  1 |    95.2 |        1 |
|  2 |    97.4 |        1 |
|  3 |    96.4 |        1 |
|  4 |    99.1 |        3 |
|  5 |    98.1 |        3 |
+----+---------+----------+

需求 #

为指定bird（如id=2的mary）返回如下结构的结果集：

+---------+-----------+--------------+--------------------------+-------------------------+--------------------+-------------------+
| bird.id | bird.name | newest check | date newest measurements | avg newest measurements | date newest weight | avg newest weight |
+---------+-----------+--------------+--------------------------+-------------------------+--------------------+-------------------+
|       2 | mary      | 2023-08-01   | 2023-08-01               |                    98.6 | 2023-07-01         |              3.52 |
+---------+-----------+--------------+--------------------------+-------------------------+--------------------+-------------------+

用户现有SQL问题 #

用户自行编写的SQL如下，卡在如何嵌套计算measurements和weight（可能关联不同Check）的平均值：

SELECT bird.id, bird.name, MAX(check.catch_date), weight
FROM bird
LEFT JOIN check ON bird.id=check.bird_id
LEFT JOIN measurements ON measurements.check_id=check.id
WHERE bird.id=2
GROUP BY id, weight;

解决方案SQL #

SELECT
    b.id AS `bird.id`,
    b.name AS `bird.name`,
    MAX(c.check_date) AS `newest check`,
    m_latest.check_date AS `date newest measurements`,
    m_latest.avg_measur AS `avg newest measurements`,
    w_latest.check_date AS `date newest weight`,
    w_latest.avg_weight AS `avg newest weight`
FROM bird b
LEFT JOIN check c ON b.id = c.bird_id
-- 子查询获取该鸟最新有measurements的检查记录及平均值
LEFT JOIN (
    SELECT
        c.bird_id,
        c.check_date,
        AVG(m.measur) AS avg_measur
    FROM check c
    JOIN measurements m ON c.id = m.check_id
    WHERE c.bird_id = 2
    ORDER BY c.check_date DESC
    LIMIT 1
) m_latest ON b.id = m_latest.bird_id
-- 子查询获取该鸟最新有weights的检查记录及平均值
LEFT JOIN (
    SELECT
        c.bird_id,
        c.check_date,
        AVG(w.weight) AS avg_weight
    FROM check c
    JOIN weights w ON c.id = w.check_id
    WHERE c.bird_id = 2
    ORDER BY c.check_date DESC
    LIMIT 1
) w_latest ON b.id = w_latest.bird_id
WHERE b.id = 2
GROUP BY b.id, b.name, m_latest.check_date, m_latest.avg_measur, w_latest.check_date, w_latest.avg_weight;

工作原理说明 #

1. 获取最新检查日期：通过MAX(c.check_date)直接提取该鸟所有检查记录中的最晚日期，对应结果集里的newest check字段。
2. 计算最新测量值平均值：
   • 子查询m_latest关联check和measurements表，筛选指定bird的记录后按check_date降序排列，取第一条就得到最新的带有测量数据的检查记录，同时用AVG(m.measur)算出该次检查下所有测量值的平均值。
3. 计算最新体重平均值：
   • 子查询w_latest和m_latest逻辑完全一致，关联check和weights表，获取最新的带有体重数据的检查记录，并算出对应体重的平均值。
4. 整合结果：把bird表和两个子查询的结果关联，最后通过分组聚合得到完全匹配需求结构的结果集。

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