MySQL多表关联查询如何获取所有活跃卡片的最新时间数据

在MySQL中执行多表关联查询时，筛选条件T2.CurrentStatus = 101代表卡片为活跃状态，原有查询语句会返回所有活跃卡片匹配到的每一条DateTime、LogTime结果，需要调整查询逻辑，仅提取所有活跃卡片对应的最新DateTime、LogTime关联数据。
原查询SQL如下：

Select 
T1.Figures, 
T1.Numbers, 
T3.Name,
T4.DateTime,
T5.Information,
T1.LogTime

From Table1 as T1

JOIN Table2 as T2 on T1.Figures = T2.Figures and T1.Numbers = T2.Numbers
JOIN Table3 as T3 on T1.CustomerID = T3.ID
join Table4 as T4 on T4.Figures = T1.Figures and T4.Numbers = T1.Numbers
join Table5 as T5 on T4.HostID = T5.ID

Where T2.CurrentStatus = 101

核心逻辑是先从Table4中按卡片唯一标识（Figures+Numbers）分组，取出每张卡片对应的最新DateTime，再用这个结果集关联其他表，避免拉取全量历史时间记录。
调整后的SQL如下：

SELECT 
T1.Figures, 
T1.Numbers, 
T3.Name,
T4.DateTime,
T5.Information,
T1.LogTime
FROM Table1 AS T1
JOIN Table2 AS T2 
  ON T1.Figures = T2.Figures 
  AND T1.Numbers = T2.Numbers
JOIN Table3 AS T3 
  ON T1.CustomerID = T3.ID
-- 关联Table4分组后得到的每张卡片最新时间子集
JOIN (
  SELECT Figures, Numbers, MAX(DateTime) AS LatestDateTime
  FROM Table4
  GROUP BY Figures, Numbers
) AS T4_Latest 
  ON T4_Latest.Figures = T1.Figures 
  AND T4_Latest.Numbers = T1.Numbers
-- 关联回Table4取最新记录对应的完整字段
JOIN Table4 AS T4 
  ON T4.Figures = T4_Latest.Figures 
  AND T4.Numbers = T4_Latest.Numbers
  AND T4.DateTime = T4_Latest.LatestDateTime
JOIN Table5 AS T5 
  ON T4.HostID = T5.ID
WHERE T2.CurrentStatus = 101;

• 若Table4中存在同一卡片同一DateTime对应多条重复记录，查询会返回多条匹配结果，可根据业务规则补充去重逻辑，或使用精度更高的排序字段（如自增主键、毫秒级时间戳）判定最新记录。
• 为Table4创建(Figures, Numbers, DateTime)联合索引，可有效提升分组、关联阶段的查询性能。

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