MySQL/MariaDB中如何获取变送器最新重复传输时段记录？

当然可以直接在MySQL/MariaDB里实现你的需求，不需要额外代码过滤。你的原查询已经能找出所有符合条件的重复数据组，但缺少对每个变送器仅保留最新时段的筛选逻辑。下面提供两种适配不同数据库版本的方法：

方法1：使用窗口函数（推荐，适用于MySQL 8.0+/MariaDB 10.2+） #

窗口函数是最简洁直观的实现方式，先聚合出所有符合条件的重复组，再给每个变送器的组按时间排序，只保留最新的那一行：

WITH duplicate_groups AS (
    SELECT 
        transmitter_id, 
        COUNT(*) AS number_of_duplicate_readings, 
        total_reading, 
        MAX(created_at) AS latest_duplicate_reading
    FROM transmissions
    GROUP BY transmitter_id, total_reading
    HAVING COUNT(*) >= 3  -- 连续3次及以上，这里用>=3更准确
)
SELECT *
FROM (
    SELECT 
        *,
        ROW_NUMBER() OVER (PARTITION BY transmitter_id ORDER BY latest_duplicate_reading DESC) AS rn
    FROM duplicate_groups
) t
WHERE rn = 1
ORDER BY transmitter_id;

逻辑说明： #

1. 首先通过CTE duplicate_groups 筛选出所有满足"连续3次及以上重复"的数据组；
2. 用 ROW_NUMBER() 窗口函数给每个transmitter_id下的组按latest_duplicate_reading降序编号，最新的组会被标记为rn=1；
3. 最后筛选出rn=1的行，就是每个变送器最新的重复读数时段。

方法2：使用关联子查询（兼容旧版本数据库） #

如果你的数据库版本不支持窗口函数，可以用关联子查询实现相同效果：

SELECT 
    dg.transmitter_id, 
    dg.number_of_duplicate_readings, 
    dg.total_reading, 
    dg.latest_duplicate_reading
FROM (
    SELECT 
        transmitter_id, 
        COUNT(*) AS number_of_duplicate_readings, 
        total_reading, 
        MAX(created_at) AS latest_duplicate_reading
    FROM transmissions
    GROUP BY transmitter_id, total_reading
    HAVING COUNT(*) >=3
) dg
INNER JOIN (
    SELECT 
        transmitter_id, 
        MAX(latest_duplicate_reading) AS max_latest_time
    FROM (
        SELECT 
            transmitter_id, 
            MAX(created_at) AS latest_duplicate_reading
        FROM transmissions
        GROUP BY transmitter_id, total_reading
        HAVING COUNT(*) >=3
    ) t
    GROUP BY transmitter_id
) latest_times 
ON dg.transmitter_id = latest_times.transmitter_id 
AND dg.latest_duplicate_reading = latest_times.max_latest_time
ORDER BY transmitter_id;

逻辑说明： #

1. 内层子查询先找出所有符合条件的重复组及其最新时间；
2. 再按transmitter_id分组，得到每个变送器的最大最新重复时间；
3. 最后通过关联查询，匹配出每个变送器对应最大最新时间的重复组。

关于原查询的补充说明 #

你的原查询已经正确聚合了重复数据组，但没有对每个变送器进行二次筛选，因此会返回同一个变送器的所有历史重复组。上面两种方法都是先获取所有符合条件的组，再通过排序或关联的方式，仅保留每个变送器最新的那一组数据。

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