MySQL内连接表获取最新2条记录的字段匹配异常问题

需查询活跃企业的最新2条记录用于可视化，要返回字段：企业名称、最新日期、最新记录的本地存储百分比、次新日期、次新记录的本地存储百分比。当前查询存在匹配错误：最新日期对应的存储百分比与次新记录的百分比一致，未正确关联对应日期的数值。

表结构 #

USERS TABLE #

DATTO Table #

当前查询语句 #

select datto.device_model,  users.company_name, max(date_recorded) as first_date, max(datto.local_storage_percent) as local_storage_percent_current,
   substring_index(substring_index(group_concat(date_recorded order by date_recorded desc), ',', 2), ',', -1) as second_date,
   substring_index(substring_index(group_concat(local_storage_percent order by local_storage_percent desc), ',', 2), ',', -1) as local_storage_percent_previous 
from datto inner join users on datto.host = users.datto_host  
where users.usractive = '1' 
group by users.company_name;

当前查询结果 #

期望查询结果 #

问题分析 #

1. 过滤条件拼写错误：原查询中users.usractive应为users.usr_active，字段名错误会导致过滤逻辑失效或报错。
2. 当前百分比取值逻辑错误：用max(local_storage_percent)取存储百分比最大值，无法关联最新日期对应的数值，因为最大值不一定属于最新记录。
3. 次新百分比排序逻辑错误：按存储百分比字符串排序拼接，而非按日期排序后关联对应数值，导致次新日期与百分比不匹配。

解决方案 #

使用窗口函数ROW_NUMBER()按企业分组、日期降序标记记录顺序，再通过条件聚合提取最新和次新的对应值：

WITH ranked_records AS (
    SELECT 
        d.device_model,
        u.company_name,
        d.date_recorded,
        d.local_storage_percent,
        ROW_NUMBER() OVER (PARTITION BY u.company_name ORDER BY d.date_recorded DESC) AS record_rank
    FROM datto d
    INNER JOIN users u ON d.host = u.datto_host
    WHERE u.usr_active = 1
)
SELECT
    device_model,
    company_name,
    MAX(CASE WHEN record_rank = 1 THEN date_recorded END) AS first_date,
    MAX(CASE WHEN record_rank = 1 THEN local_storage_percent END) AS local_storage_percent_current,
    MAX(CASE WHEN record_rank = 2 THEN date_recorded END) AS second_date,
    MAX(CASE WHEN record_rank = 2 THEN local_storage_percent END) AS local_storage_percent_previous
FROM ranked_records
WHERE record_rank <= 2
GROUP BY company_name, device_model;

说明 #

• ROW_NUMBER()为每个企业的记录按日期从新到旧标记序号，1对应最新记录，2对应次新记录。
• 条件聚合CASE WHEN精准提取不同序号对应的日期和存储百分比，确保日期与数值正确关联。
• 修正了原查询中usr_active的拼写错误，正确过滤活跃企业。

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