基于中间表的猴子相关数据MySQL查询实现方案

需求说明 #

从MONKEY、CHECK、MEASUREMENTS、XMITTER四张表中，为每只猴子返回一条包含以下信息的记录：

• 最近检查日期
• 最近检查中多次身长/身宽测量的平均值，以及仅记录一次的体重
• 当前未移除（removed字段为NULL）的发射器信息

实现SQL #

WITH LatestCheck AS (
    -- 筛选每只猴子的最近检查记录
    SELECT 
        m.monkey_id,
        c.check_id,
        c.check_date AS latest_check_date
    FROM MONKEY m
    LEFT JOIN `CHECK` c ON m.monkey_id = c.monkey_id
    WHERE (m.monkey_id, c.check_date) IN (
        SELECT monkey_id, MAX(check_date)
        FROM `CHECK`
        GROUP BY monkey_id
    )
),
LatestMeasureStats AS (
    -- 计算最近检查的身长/身宽平均值，提取体重数据
    SELECT 
        lc.monkey_id,
        AVG(CASE WHEN me.measurement_type IN ('length', 'width') THEN me.value END) AS avg_length_width,
        MAX(CASE WHEN me.measurement_type = 'weight' THEN me.value END) AS weight
    FROM LatestCheck lc
    LEFT JOIN MEASUREMENTS me ON lc.check_id = me.check_id
    GROUP BY lc.monkey_id
),
ActiveXmitters AS (
    -- 整理未移除的发射器信息，多台则合并为字符串
    SELECT 
        monkey_id,
        GROUP_CONCAT(CONCAT(xmitter_id, ' - ', model) SEPARATOR ', ') AS active_xmitter_info
    FROM XMITTER
    WHERE removed IS NULL
    GROUP BY monkey_id
)
-- 关联所有结果，输出每只猴子的完整信息
SELECT 
    m.monkey_id,
    m.name,
    lms.latest_check_date,
    lms.avg_length_width,
    lms.weight,
    ax.active_xmitter_info
FROM MONKEY m
LEFT JOIN LatestMeasureStats lms ON m.monkey_id = lms.monkey_id
LEFT JOIN ActiveXmitters ax ON m.monkey_id = ax.monkey_id
ORDER BY m.monkey_id;

逻辑说明 #

• LatestCheck：通过分组取最大检查日期，锁定每只猴子的最近检查记录
• LatestMeasureStats：用条件聚合计算身长/身宽的平均值，同时提取唯一的体重数据
• ActiveXmitters：筛选未移除的发射器，用GROUP_CONCAT合并同一猴子的多台发射器信息，保证单条记录输出
• 最终通过LEFT JOIN关联所有数据，确保即使部分字段无数据，也能保留每只猴子的记录

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