Ms Access -连接两个表并获取最后一个记录。

Question

我有两个表，名为Reefers和FilteredAlerts。我想加入这两张桌子。

如何获得状态=“加载”和GPSAlertype =“温度”的每个Reefers的最后记录？

​

​

正如你在结果中看到的，ReeferNo R-3没有显示，因为GPSAlertType不等于温度。我使用下面的代码，但总是给我一个错误。

 SELECT
   r.ReeferNo,
   r.TransporterName,
   r.Status, 
   sub2.AlertDateTime,
   sub2.ReceivedDateTime,
   sub2.GPSAlertType,
   sub2.Temperature,
   sub2.Location
FROM
   Reefers AS r
   LEFT JOIN
   (
      SELECT
         sub1.ReeferNo,
         a2.AlertDateTime,
         a2.ReceivedDateTime,
         a2.GPSAlertType,
         a2.Temperature,
         a2.Location
     FROM
         (
             SELECT
                 a1.ReeferNo,
                 Max(a1.AlertDateTime) AS MaxOfAlertDateTime
             FROM FilteredAlerts AS a1
             WHERE a1.GPSAlertType='Temperatures' AND r.Status = 'Loaded'
             GROUP BY a1.ReeferNo
         ) AS sub1
         INNER JOIN FilteredAlerts AS a2
         ON
                (sub1.MaxOfAlertDateTime = a2.AlertDateTime)
            AND (sub1.ReeferNo = a2.ReeferNo)
     ) AS sub2
 ON r.ReeferNo = sub2.ReeferNo;

提前谢谢你的帮助。

Answer 1

给你：

SELECT
    r2.ReeferNo,
    r2.TransporterName,
    r2.Status,
    a2.AlertDateTime,
    a2.ReceivedDateTime,
    a2.GPSAlertType,
    a2.Temperature,
    a2.Location
FROM ((SELECT r.ReeferNo, Max(a.AlertDateTime) as AlertDateTime
    FROM Reefers r LEFT JOIN FilteredAlerts a ON r.ReeferNo = a.ReeferNo
    WHERE
        r.Status="Loaded" AND 
        a.GPSAlertType='Temperature'
    GROUP BY r.ReeferNo
) s LEFT JOIN FilteredAlerts a2 ON 
    s.ReeferNo = a2.ReeferNo and
    s.AlertDateTime = a2.AlertDateTime
) INNER JOIN Reefers r2 ON s.ReeferNo = r2.ReeferNo
WHERE a2.GPSAlertType='Temperature' and
    r2.Status='Loaded';