mysql - 选择具有最新时间戳的记录

Question

我有两个表，从中选择用户、系统和软件。

软记录应该是最新的“tstamp2”

第一:表系统

USER       SYSTEM           ltstamp           
======-----======----===================
User1       LA1      2013-05-06 11:27:26
User2       LA2      2013-06-07 11:27:26 

第二:表格软件

Soft     SYSTEM            tstamp2
=====----=====------===================
  Av1      LA1       2013-04-06 10:27:26
  Av2      LA1       2013-05-06 11:27:26
  Av1      LA2       2013-04-06 10:27:26
  Av2      LA2       2013-06-07 11:27:26

Answer 1

SELECT s.user, s.system, sw.max_tstamp, sw2.soft
FROM
  systems s INNER JOIN (SELECT system, MAX(tstamp2) AS max_tstamp
                        FROM software
                        GROUP BY system) sw
  ON s.system = sw.system INNER JOIN software sw2
  ON s.system = sw2.system AND sw.max_tstamp=sw2.tstamp2

请参阅 fiddle here .