按日期范围选择最大日期 [英] Select Max Date by Date Range

问题描述

我有一个检查表，我正在尝试选择所需的每个日期范围的最大日期.这是在MS Access 2007查询中，而我发现的大多数答案都是T SQL格式.

I have a table Inspection and I'm trying to select the max date per date range I want. This is in MS Access 2007 query and most answers I found is in T SQL format.

例如日期范围:#1/1/2012#至#3/30/2012#

For example Date Range: Between #1/1/2012# AND #3/30/2012#

Date       PlateNo     TickeStatus
01-15-12       A        Open
01-23-12       c        Open
01-17-12       A        Close
02-23-12       c        Open
02-15-12       A        Open
03-23-12       c        Open
03-15-12       A        Close
03-28-12       c        Close
04-15-12       A        Open
04-23-12       c        Close

在我设置的日期范围内.我想要的结果是这样的:

In the date range I set. the result I want is like this:

Date       PlateNo     TickeStatus
03-15-12       A        Close
03-28-12       c        Close

推荐答案

此查询将为您提供目标日期范围内每个PlateNo的最大Date.

This query will give you the maximum Date for each PlateNo within your target date range.

SELECT i.PlateNo, Max(i.Date) AS MaxOfDate
FROM inspections AS i
WHERE i.Date BETWEEN #2012-1-1# AND #2012-3-30#
GROUP BY i.PlateNo;

要获取相应的TickeStatus值，请将其用作子查询，并连接到检查表.

To get the corresponding TickeStatus values, use that as a subquery which you join to the inspections table.

SELECT i2.Date, i2.PlateNo, i2.TickeStatus
FROM
    inspections AS i2
    INNER JOIN
        (
            SELECT i.PlateNo, Max(i.Date) AS MaxOfDate
            FROM inspections AS i
            WHERE i.Date BETWEEN #2012-1-1# AND #2012-3-30#
            GROUP BY i.PlateNo
        ) AS sub
    ON
            (i2.PlateNo = sub.PlateNo)
        AND (i2.Date = sub.MaxOfDate);

这篇关于按日期范围选择最大日期的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！