从查询开始,以查找每个[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