MS Access中的子查询：每个日期只选择一个“person”记录

Question

我正在使用Microsoft Access中分析的表。它有很多字段，但是在这种情况下用于过滤的三个字段分析.reads_miu_id，analyze.ReadDate，analyze.ReadTime。我需要从“分析”表中提取记录，其中，将readings_miu_id分组在一起，然后按ReadDate进行排序，但只显示该日期的最后一个条目，这可能是ReadTime中最高值的时间。我创建了一个查询：

SELECT readings_miu_id, Reading, ReadDate, ReadTime, 
    MIUwindow, SN, Noise, RSSI, OriginCol, ColID, Ownage 
FROM analyzed 
WHERE analyzed.ReadDate Between #4/21/2009# and #4/29/2009# 
AND analyzed.ReadTime= ( 
    SELECT TOP 1 analyzed.ReadTime 
    FROM analyzed 
    WHERE analyzed.readings_miu_id = *????*** 
    ORDER BY analyzed.ReadTime DESC); 

* ???? ***是用来证明我不是一定要在这里放什么

，如果我在一个有效readings_miu_id进入我得到一个记录这是readings_miu_id的最高时间。

如何使用像上面那样的子查询来为每个ReadDate只读取每个readings_miu_id的最后ReadTime？

readings_miu_id不表ID，它更类似于项目编号或名称

Answer 1

SELECT readings_miu_id 
    , Reading , ReadDate , ReadTime 
    , MIUwindow, SN, Noise, RSSI 
    , OriginCol, ColID, Ownage 
FROM analyzed AS A 
    WHERE analyzed.ReadDate Between #4/21/2009# and #4/29/2009# 
     AND analyzed.ReadTime= 
      (SELECT TOP 1 analyzed.ReadTime FROM analyzed 
      where analyzed.readings_miu_id = A.readings_miu_id 
       AND analyzed.ReadDate = A.ReadDate 
      ORDER BY analyzed.ReadTime DESC); 

我只是别名主表为A，只是refered给它的子查询。不确定是否需要在子查询中通过ReadDate进行筛选。

Answer 2

这将让你关闭。声明“where readings_miu_id被分组在一起”令我感到困惑。不知道您是否想要GroupBy或该字段上的排序。此查询将为您提供分析表中具有相同日期的记录，但是该日期的“最大：”时间。听起来像是你想要的。

SELECT readings_miu_id, Reading, ReadDate, ReadTime, MIUwindow, SN, Noise, RSSI, OriginCol, ColID, Ownage 
FROM analyzed , 
(
    Select ReadDate as matchDate, Max(ReadTime) as matchTime 
    FROM analyzed 
    Where analyzed.ReadDate Between #4/21/2009# and #4/29/2009# 
    Group by ReadDate 
) dateAndTime 
WHERE analyzed.ReadDate Between #4/21/2009# and #4/29/2009# AND ReadDate = dateAndTime.matchDate AND readTime = dateAndTime.matchTime 
Order By readDate