Ms Access - Join 2表提供重复记录

Question

我的查询有什么问题？我想加入2个表，并获得每个冷藏箱的最后记录，其中的GPSAlertType=“温度”。

冷藏表：

ReeferNo    TransporterName   Status
  R-1            ABC           Empty
  R-2            ABC           Empty
  R-3            ABC           Loaded
  R-4            ABC           Empty

FilteredAlerts表：

ReeferNo     AlertDateTime  ReceivedDateTime    GPSAlertType    Temperature   Location
  R-1        23/07/15 06:00   7/23/15 6:03      Temperature      7.05        Warehouse
  R-2        23/07/15 06:02   7/23/15 6:05      Arrival                      Warehouse
  R-3        24/07/15 10:37   7/24/15 10:39     Temperature      5.81        Store
  R-4        24/07/15 10:39   7/24/15 10:41     Departure                    Warehouse

结果应该是：

ReeferNo    TransporterName  Status AlertDateTime   ReceivedDateTime    GPSAlertType    Temperature  Location
 R-1            ABC          Empty  23/07/15 06:00  7/23/15 6:03        Temperature         7.05     Warehouse
 R-2            ABC          Empty                  
 R-3            ABC          Loaded 24/07/15 10:37  7/24/15 10:39       Temperature         5.81     Store
 R-4            ABC          Empty                  

我正在使用下面的查询，但它给了我重复的ReeferNo。

SELECT DISTINCT 
       r.ReeferNo, 
       r.TransporterName, 
       r.Status,
       sub2.AlertDateTime, 
       sub2.ReceivedDateTime, 
       sub2.GPSAlertType, 
       sub2.Temperature, 
       sub2.Location
FROM 
       Reefers AS r 
       LEFT JOIN 
       (
            SELECT DISTINCT 
                   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='Temperature' 
                       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;

已有查询结果：

ReeferNo    TransporterName  Status   AlertDateTime    ReceivedDateTime GPSAlertType  Temperature  Location
R-1            ABC            Empty   23/07/15 06:00    7/23/15 6:03    Temperature     7.05        Warehouse
R-2            ABC            Empty             
R-3            ABC                    24/07/15 10:37    7/24/15 10:39   Temperature     5.81        Store
R-3            ABC            Loaded  24/07/15 10:37    7/24/15 10:39   Temperature     5.81        Store
R-4            ABC            Empty                 

谢谢你的帮助。

Answer 1

尝试一下，简单地将where子句与内连接查询一起使用

SELECT DISTINCT 
   r.ReeferNo, 
   r.TransporterName, 
   r.Status,
   sub2.AlertDateTime, 
   sub2.ReceivedDateTime, 
   sub2.GPSAlertType, 
   sub2.Temperature, 
   sub2.Location
FROM 
   Reefers AS r 
   INNER JOIN FilteredAlerts AS sub2
   ON r.ReeferNo = sub2.ReeferNo
   AND sub2.GPSAlertType='Temperature' 

Answer 2

问题出在我的数据库里。我发现有重复记录的reeferno。我删除了副本，现在它可以正常工作了。

很抱歉。