MySQL多表查询需求：匹配mID并返回tstamp最大值对应记录

我来帮你搞定这个SQL查询！你的需求核心是要合并两张表的所有记录，并且对同时存在于两张表的mID，只保留tstamp最大的那条，这个可以通过UNION ALL结合分组取最大值的方式实现。

实现思路 #

1. 先把两张表中需要的字段（mID, mtype, tstamp）合并成一个临时数据集，我们不需要Alpha表的source和state字段，所以只提取需要的列即可。
2. 对这个合并后的数据集按mID分组，计算每个mID对应的最大tstamp值。
3. 最后将合并后的数据集和这个最大tstamp的结果关联，筛选出每个mID中tstamp等于最大值的记录，这样就得到了符合要求的结果。

完整SQL语句 #

WITH combined_data AS (
    SELECT mID, mtype, tstamp FROM Alpha
    UNION ALL
    SELECT mID, mtype, tstamp FROM Bravo
), max_tstamp AS (
    SELECT mID, MAX(tstamp) AS max_ts FROM combined_data GROUP BY mID
)
SELECT cd.mID, cd.mtype, cd.tstamp
FROM combined_data cd
JOIN max_tstamp mt ON cd.mID = mt.mID AND cd.tstamp = mt.max_ts
ORDER BY cd.tstamp;

语句解释 #

• combined_data CTE：用UNION ALL把两张表的记录合并，保留所有原始记录（包括重复的mID记录），这样不会丢失任何数据。
• max_tstamp CTE：按mID分组，找出每个mID的最新时间戳，这一步是为了确定每个mID要保留哪条记录。
• 最后关联两个CTE：通过mID和tstamp匹配，筛选出每个mID对应的最新记录，同时按tstamp排序让结果和你给出的预期顺序一致。

验证结果 #

这条语句会准确返回你想要的结果：

• 只在Alpha存在的1111A-1111A-1111A-1111A直接保留；
• 只在Bravo存在的1111A-1111A-2222A-2222A直接保留；
• 同时存在的mID（比如3333A）会取tstamp更大的那条（Bravo的1590000021000），对应的mtype也会正确带出。

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