MySQL按两列分组取最新数据并生成历史状态二进制列的实现问题

问题原因 #

你当前的查询先通过WHERE子句过滤出了每个分组最新日期对应的记录，后续对Has_sent、Has_received的判断仅能基于这部分过滤后的记录，无法覆盖该分组的全量历史数据，因此结果不符合预期。

兼容所有MySQL版本的实现语句 #

SELECT 
    t1.Letters,
    t1.Numbers,
    t_group.latest_date,
    t1.score AS latest_score,
    t_group.Has_sent,
    t_group.Has_received
FROM dummy_data t1
INNER JOIN (
    -- 先按分组聚合得到全量历史的状态标记和最新日期
    SELECT 
        Letters,
        Numbers,
        MAX(dates) AS latest_date,
        MAX(CASE WHEN random_status = 'Sent' THEN 1 ELSE 0 END) AS Has_sent,
        MAX(CASE WHEN random_status = 'Received' THEN 1 ELSE 0 END) AS Has_received
    FROM dummy_data
    GROUP BY Letters, Numbers
) t_group 
ON t1.Letters = t_group.Letters 
AND t1.Numbers = t_group.Numbers 
AND t1.dates = t_group.latest_date
GROUP BY t1.Letters, t1.Numbers;

MySQL 8.0+ 窗口函数简化实现 #

如果你的MySQL版本支持窗口函数（8.0及以上），可以用更简洁的写法：

WITH ranked_data AS (
    SELECT 
        Letters,
        Numbers,
        dates AS latest_date,
        score AS latest_score,
        MAX(CASE WHEN random_status = 'Sent' THEN 1 ELSE 0 END) OVER (PARTITION BY Letters, Numbers) AS Has_sent,
        MAX(CASE WHEN random_status = 'Received' THEN 1 ELSE 0 END) OVER (PARTITION BY Letters, Numbers) AS Has_received,
        ROW_NUMBER() OVER (PARTITION BY Letters, Numbers ORDER BY dates DESC) AS rn
    FROM dummy_data
)
SELECT Letters, Numbers, latest_date, latest_score, Has_sent, Has_received
FROM ranked_data
WHERE rn = 1;

实现逻辑说明 #

• 两种写法都是先对每个Letters+Numbers分组的全量历史记录做判断：只要存在任意一条random_status为Sent的记录，Has_sent就为1，否则为0；Has_received逻辑同理。
• 之后再匹配每个分组最新日期对应的score，最终输出结果和你给出的期望输出完全一致。

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