sql - SQL从只有1个日期的表中选择最大日期

Question

我有这个 SQL 代码：

SELECT MH.[PM#], 
       MH.[Work Done], 
       MH.[Date Done], 
       MH.Comments, 
       MH.[SEM Completed by]
 FROM [Maintenance History tbl] AS MH
WHERE (((MH.[Work Done])="LT") 
  AND ((MH.[Date Done]) =
        ( SELECT Max(MH2.[Date Done])
            FROM [Maintenance History tbl] AS MH2
           WHERE MH2.[PM#] = MH.[PM#]
        )
      ));

这将选择完成日期最近的四个字段。

如果只有 1 行，这将不起作用。

如果只有 1 行，我怎样才能让它返回最大日期？

标签： sqlms-accessms-access-2007

Answer 1

我认为您错过了Work Done = "LT"子查询中的过滤条件。尝试添加它：

SELECT MH.[PM#], 
       MH.[Work Done], 
       MH.[Date Done], 
       MH.Comments, 
       MH.[SEM Completed by]
 FROM [Maintenance History tbl] AS MH
WHERE (MH.[Work Done] = "LT"
  AND (MH.[Date Done] =
        ( SELECT Max(MH2.[Date Done])
            FROM [Maintenance History tbl] AS MH2
           WHERE MH2.[PM#] = MH.[PM#]
             and MH2.[Work Done] = "LT"
        )
      ));