sql - 子查询返回 1 个结果，因此结果保持不同

Question

我有这个 SQL:

SELECT a.ID,
       a.title,
       a.section,
       a.name,
       l.User AS CreatedBy,
       IIf(IsNull(l.Time),
             Null, DateAdd ( "s", l.time, #03/01/1980# )) AS CreatedAt
FROM (
       Reports AS a
       LEFT JOIN AuditLog AS l ON a.ID = l.ID
     ) 
     LEFT JOIN 
      (SELECT TOP 1 Min([time]) AS Mintime,
       URN FROM AuditLog GROUP BY ID)
      AS t ON (l.time = t.mintime) AND (l.ID = t.ID)
WHERE a.Active = 'Y';

查询有效，但返回重复记录(因为 1 份报告有多个审计日志)。我只想返回一个显示最早审计时间的 ID 记录(因此 mintime 是什么)。我添加了 TOP 1 但这似乎没有任何改变。我也考虑过 GROUP BY ID 但对如何仍然显示 title 等有点困惑，因为它提示它们不是聚合的。

当前输出:

ID         title        section     CreatedBy     CreatedAt
Z.test1    Example 1    Ex          User1         01/01/2012
Z.test5    Example 5    Ex          User2         02/03/2012
Z.test6    Example 6    Ex          User3         03/06/2012
Z.test6    Example 6    Ex          User4         02/01/2000

预期输出:

ID         title        section     CreatedBy     CreatedAt
Z.test1    Example 1    Ex          User1         01/01/2012
Z.test5    Example 5    Ex          User2         02/03/2012
Z.test6    Example 6    Ex          User4         02/01/2000

如上所示，只显示了Z.test6的最早记录，并没有显示所有结果。

尝试新查询:

SELECT a.ID, a.title, a.section, a.name, l.User AS CreatedBy,   IIf(IsNull(l.Time), Null, DateAdd ( "s", l.time, #03/01/1980# )) AS CreatedAt
FROM (
              Reports AS a 
LEFT JOIN (
                    SELECT ID, Min([time]) AS Mintime FROM AuditLog GROUP BY ID
                  )  AS t ON (l.ID = t.ID)
              LEFT JOIN AuditLog AS l ON (a.ID = l.ID AND t.mintime = l.time)
)
WHERE a.Active = 'Y'

Answer 1

交换连接顺序，只选择分组所需的子查询“t”中的最低要求字段。像这样:

SELECT a.ID,
   a.title,
   a.section,
   a.name,
   l.User AS CreatedBy,
   IIf(IsNull(l.Time),
         Null, DateAdd ( "s", l.time, #03/01/1980# )) AS CreatedAt
FROM (
   Reports AS a
   LEFT JOIN (SELECT ID, Min([time]) AS Mintime
              FROM AuditLog GROUP BY ID) AS t 
     ON (a.ID = t.ID)
   LEFT JOIN AuditLog AS l 
     ON (t.ID = l.ID AND t.mintime = l.time)
 ) 
WHERE a.Active = 'Y';