MySQL INNER JOIN查询：如何获取每个tbl_device.DevID的最后3条结果

Hey there! Let's figure out how to fetch the last 3 records for each DevID in your tbl_device table, even while keeping your existing INNER JOIN logic intact. I'll share two practical approaches tailored to different MySQL versions:

方法1：使用窗口函数（MySQL 8.0及以上版本） #

This is the cleanest and most efficient way, leveraging the ROW_NUMBER() window function to rank records per device:

SELECT *
FROM (
    SELECT 
        -- 替换成你实际需要的字段，包括关联表的字段
        d.DevID, d.device_name, ot.related_data,
        -- 按DevID分组，按时间字段降序排序（确保最新记录排在最前面）
        ROW_NUMBER() OVER (PARTITION BY d.DevID ORDER BY d.your_timestamp_column DESC) AS row_num
    FROM tbl_device d
    -- 在这里插入你的INNER JOIN语句，比如：
    -- INNER JOIN your_joined_table ot ON d.join_key = ot.join_key
) AS ranked_records
-- 筛选每个DevID的前3条最新记录
WHERE row_num <= 3;

关键说明： #

• Replace your_timestamp_column with the field you use to determine record order (like create_time or update_time).
• PARTITION BY d.DevID groups data by device ID, while ORDER BY ... DESC ensures the newest records in each group come first.
• The outer query filters to keep only the top 3 ranked records per DevID.

方法2：适用于MySQL 5.7及更早版本（无窗口函数支持） #

If you're working with an older MySQL version that doesn't support window functions, a correlated subquery will do the trick:

SELECT 
    d.DevID, d.device_name, ot.related_data -- 替换成你需要的字段
FROM tbl_device d
-- 插入你的INNER JOIN语句
-- INNER JOIN your_joined_table ot ON d.join_key = ot.join_key
WHERE (
    SELECT COUNT(*)
    FROM tbl_device d2
    WHERE d2.DevID = d.DevID
      -- 统计当前记录及时间更晚的同设备记录数
      AND d2.your_timestamp_column >= d.your_timestamp_column
      -- 若存在时间完全重复的记录，加上主键判断确保精确返回3条：
      -- AND (d2.your_timestamp_column > d.your_timestamp_column OR (d2.your_timestamp_column = d.your_timestamp_column AND d2.id > d.id))
) <= 3;

关键说明： #

• The subquery counts how many records in the same DevID group have a timestamp greater than or equal to the current record. The newest 3 records will have a count ≤3, so they get filtered in.
• If multiple records share the same timestamp, adding a primary key comparison (like id) ensures you don't end up with more than 3 records per DevID.

Just plug your existing INNER JOIN conditions into either query, and you'll get the result you're looking for!

内容的提问来源于stack exchange，提问作者Ariz Sumagui