我有一个可访问的数据库,可在其中记录借出的地图。因此,我有一个名为MapsOut的表,其中包含字段MapNum(地图ID),MapName(地图名称),CheckOut(将地图借出的日期),CheckIn(将地图返回的日期)。
MapNum MapName CheckOut CheckIn 1 London 01/02/13 07/05/13 1 London 08/05/13 16/06/13 1 London 19/07/13 2 Hull 30/01/13 05/03/13 2 Hull 06/04/13 01/05/13 3 Derby 11/01/13 17/02/13 3 Derby 05/09/13 06/10/13 4 Hove 01/02/13 01/03/13
我想编写一个查询,该查询只给我每个MapNum的最后一条记录,而只显示那些已签入的记录,因此我知道哪个是最近的,并且以最近到最长的时间顺序排列。 CheckOut列。因此结果应如下所示:
MapNum MapName CheckOut CheckIn 4 Hove 01/02/13 01/03/13 2 Hull 06/04/13 01/05/13 3 Derby 05/09/13 06/10/13
我进行了查询,但是无法显示Select DISTINCT,因为它仍然显示重复项。
这是行不通的:
SELECT DISTINCT Maps.MapNum AS MapNum, Maps.MapName, Max(MapsOut1.CheckOut) AS CheckOut, MapRecords.CheckIn FROM (MapRecords INNER JOIN Maps ON MapRecords.MapNum = Maps.MapNum) INNER JOIN (MapsOut INNER JOIN MapsOut1 ON MapsOut.ID = MapsOut1.ID) ON Maps.MapNum = MapsOut.MapNum GROUP BY Maps.MapNum, Maps.MapName, MapRecords.CheckIn HAVING (((MapRecords.CheckIn) Is Not Null)) ORDER BY Maps.MapNum;
任何帮助将不胜感激。
提前致谢
从查询开始,以查找每个[MapNum]的最新条目
SELECT MapNum, Max(CheckOut) AS MaxOfCheckOut FROM MapData GROUP BY MapNum
返回
MapNum MaxOfCheckOut ------ ------------- 1 2013-07-19 2 2013-04-06 3 2013-09-05 4 2013-02-01
我们可以将其用作子查询,以返回这些行中每行的其余字段,但前提是[CheckIn]不为Null
SELECT md.MapNum, md.MapName, md.CheckOut, md.CheckIn FROM MapData md INNER JOIN ( SELECT MapNum, Max(CheckOut) AS MaxOfCheckOut FROM MapData GROUP BY MapNum ) AS mx ON md.MapNum = mx.MapNum AND md.CheckOut = mx.MaxOfCheckOut WHERE md.CheckIn IS NOT NULL ORDER BY md.CheckOut DESC
MapNum MapName CheckOut CheckIn ------ ------- ---------- ---------- 3 Derby 2013-09-05 2013-10-06 2 Hull 2013-04-06 2013-05-01 4 Hove 2013-02-01 2013-03-01