最大日期子查询SQL Server

Question

我试图在子查询中加入一个最大日期，但是我得到了一个错误。我在使用SQL服务器

SELECT Headcount.[Snapshot Date]
, Headcount.[ID]
, Assessment.[Assessment Date]
, Assessment.[Code]

FROM Headcount INNER JOIN Assessment ON Assessment.[ID] = Headcount.[ID]
WHERE Assessment.[Assessment Date] = 
                 (SELECT MAX(CAST(Assessment.[Assessment Date] as datetime)) 
                  FROM Assessmnet AS Z 
                  WHERE Z.[ID] = Assessment.[ID] 
                       AND Assessment.[Assessment Date] <= Headcount.[Snapshot Date]
                 )

但我发现了一个错误：

聚合可能不会出现在WHERE子句中，除非它位于包含在HAVING子句或select列表中的子查询中，并且正在聚合的列是外部引用。

Answer 1

您忘记了您在子查询中指定的别名Z。如果您在子查询中使用Assessment，那么他会考虑外部查询中的Assessment，因为Assessment在子查询中有名称Z。

SELECT Headcount.[Snapshot Date]
, Headcount.[ID]
, Assessment.[Assessment Date]
, Assessment.[Code]

FROM Headcount INNER JOIN Assessment ON Assessment.[ID] = Headcount.[ID]
WHERE Assessment.[Assessment Date] = 
                 (SELECT MAX(CAST(Z.[Assessment Date] as datetime)) 
                  FROM Assessment AS Z 
                  WHERE Z.[ID] = Assessment.[ID] 
                       AND Z.[Assessment Date] <= Headcount.[Snapshot Date]
                 )

Answer 2

问题在where条件的子查询中，请尝试如下所示：

SELECT
    Headcount.[Snapshot Date],
    Headcount.[ID],
    Assessment.[Assessment Date],
    Assessment.[Code]
FROM Headcount
INNER JOIN Assessment ON Assessment.[ID] = Headcount.[ID]
WHERE Assessment.[Assessment Date] =
(
    SELECT
        MAX(CAST(Assessment.[Assessment Date] AS DATETIME))
    FROM Assessmnet AS Z
    GROUP BY
        Z.ID
    HAVING Z.[ID] = Assessment.[ID]
           AND Assessment.[Assessment Date] <= Headcount.[Snapshot Date]
);

Answer 3

您应该编写MAX(CAST(Z.[Assessment Date] as datetime))而不是MAX(CAST(Assessment.[Assessment Date] as datetime))。否则，您将在错误的表上进行聚合。

以下查询可能有效(未经测试！)：

SELECT Headcount.[Snapshot Date]
, Headcount.[ID]
, Assessment.[Assessment Date]
, Assessment.[Code]
FROM Headcount
INNER JOIN Assessment ON Assessment.[ID] = Headcount.[ID]
WHERE Assessment.[Assessment Date] =
                 (SELECT MAX(CAST(Z.[Assessment Date] as datetime))
                  FROM Assessment AS Z
                  WHERE Z.[ID] = Assessment.[ID] 
                    AND Z.[Assessment Date] <= Headcount.[Snapshot Date])

您还可以重写它以使用数为每个评估选择最近的行：

SELECT [Snapshot Date], [ID], [Assessment Date], [Code]
FROM (SELECT [Snapshot Date], [ID], [Assessment Date], [Code],
        ROW_NUMBER() OVER (PARTITION BY [ID] ORDER BY [Assessment Date] DESC) rn
      FROM Headcount JOIN Assessment USING ([ID])
      WHERE [Assessment Date] <= [Snapshot Date]) t
WHERE rn = 1