如何在每个“人”中仅选择一条记录，每个日期在MS Access查询中使用内部联接？

I need to pull data from two tables: Neptune_FN_Analysis and Neptune_prem There will be 3 fields called readings_miu_id (comparable to a persons name or item #), ReadDate, ReadTime (all of which are in Neptune_FN_Analysis). Some readings_miu_ids have multiple ReadTimes for multiple days but I only want to pull the "last time" entered per readings_miu_id, per day.
I need all readings_miu_ids that have an entry date for the selected range but only the last ReadTime entered for each record I am pulling.

我需要从两个表中提取数据:Neptune_FN_Analysis和Neptune_prem将有3个字段称为readings_miu_id(与人名或项目#相当),ReadDate,ReadTime(所有这些都在Neptune_FN_Analysis中)。有些readings_miu_ids有多天的多个ReadTime,但我只想每天读取每个readings_miu_id的“最后一次”。我需要所有readings_miu_ids,它们具有所选范围的输入日期,但只有我为每个记录输入的最后一次ReadTime。

My solution so far, based on one table is:

到目前为止,我的解决方案基于一个表是:

SELECT readings_miu_id, Reading, ReadDate, ReadTime, MIUwindow, SN, Noise, RSSI, OriginCol, ColID, Ownage
FROM analyzed AS A
WHERE ReadDate Between #4/21/2009# and #4/29/2009# 
AND 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);

When I try to adapt this solution, I can't do the FROM [tableName] as A, INNER JOIN because it gives me an error. The original code that my predecessor made (which is what I am trying to adapt/fix) is as follows:

当我尝试调整此解决方案时,我不能将FROM [tableName]作为A,INNER JOIN,因为它给了我一个错误。我的前任制作的原始代码(我正在努力调整/修复)如下:

SELECT readings_miu_id, Reading, ReadDate,Format([MIUtime],'hh:mm:ss') AS 
ReadTime, MIUwindow, SN, Noise, RSSI, ColRSSI, MIURSSI, Firmware, CFGDate, FreqCorr, 
Active, MeterType, OriginCol, ColID, Ownage, SiteID, PremID, Neptune_prem.prem_group1, 
Neptune_prem.prem_group2, ReadID 
INTO analyzed  
FROM Neptune_FN_Analysis INNER JOIN 
Neptune_prem ON Neptune_FN_Analysis.PremID = Neptune_prem.premice_id 
WHERE  SiteID = 36801 and ReadDate BETWEEN #04/21/09# AND #04/27/09#  
and OriginCol = 'US 29'    and ColID = 1 and ColID <> 0 and Active = 'Y'

4 个解决方案

#1

I don't quite get all of what you're trying to do, but if you inner join on a subquery which gets the MAX of date, it could eliminate all the records where the date was not the max

我不太了解你想要做的所有事情,但如果你内心加入得到日期最大值的子查询,它可以消除日期不是最大值的所有记录

SELECT readings_miu_id, Reading, ReadDate, ReadTime, MIUwindow, SN, 
Noise, RSSI, OriginCol, ColID, Ownage 
FROM analyzed 
INNER JOIN 
(SELECT [whatever the id common to all like records is] as likeID, MAX(analyzed.ReadTime) as latestDate
 FROM analyzed 
 GROUP BY likeID) AS maxDate ON analyzed.likeID=maxDate.likeID AND analyzed.latestDate = maxDate.latestDate
WHERE ReadDate Between #4/21/2009# and #4/29/2009#

modify as needed

根据需要修改

#2

I would try something like this:

我会尝试这样的事情:

SELECT a.readings_miu_id, a.Reading, a.ReadDate, a.ReadTime, a.MIUwindow, a.SN, a.Noise, a.RSSI, a.OriginCol, a.ColID, a.Ownage
FROM analyzed AS A INNER JOIN
          (SELECT max(ReadTime) as MaxReadTime,readings_miu_id FROM analyzed 
             WHERE ReadDate Between #4/21/2009# and #4/29/2009#
              GROUP BY readings_miu_id) as B 
             on a.readings_miu_id = b.readings_miu_id  and a.MaxReadTime = b.ReadTime

#3

SELECT
     <your columns>
FROM
     Neptune_FN_Analysis A1
INNER JOIN Neptune_prem ON
     P.premice_id = A1.PremID
LEFT OUTER JOIN Neptune_FN_Analysis A2 ON
     A2.readings_miu_id = A1.readings_miu_id AND
     A2.ReadDate = A1.ReadDate AND
     A2.ReadTime > A1.ReadTime
WHERE
     A2.readings_miu_id IS NULL AND
     <add any additional criteria here>

#4

I'm not sure what you imply by specifying "INNER JOIN" this time around. Other answers use a subquery, so here's a an example using two INNER JOINs and no subquery. Rather than getting my head around your schema :) I'm using Northwind to return customers and the date of their most recent order:

我不确定你这次指定“INNER JOIN”意味着什么。其他答案使用子查询,所以这是一个使用两个INNER JOIN并且没有子查询的示例。而不是让我的头脑围绕你的架构:)我正在使用Northwind返回客户和他们最近的订单的日期:

SELECT C1.CustomerID, C1.CompanyName, 
       O1.OrderID, O1.OrderDate
  FROM (Customers AS C1
       INNER JOIN Orders AS O1
          ON C1.CustomerID = O1.CustomerID)
       INNER JOIN Orders AS O2
          ON C1.CustomerID = O2.CustomerID
 GROUP 
    BY C1.CustomerID, C1.CompanyName, 
       O1.OrderID, O1.OrderDate
HAVING O1.OrderDate = MAX(O2.OrderDate);

#1