mysql - 执行查询时出现问题，在 SQL 的 WHERE 中使用 MAX

Question

我正在尝试查询日期，当我尝试使用字段的最大日期时出现问题，因为我认为在使用 MAX 运算符时我应该使用 having，但我不知道如何解决。

SELECT SubscriberKey AS IdContact,
    SUM(ord.SalePrice) / COUNT(line.IdOrderLocator) AS SpentOlderSeason,
    COUNT(DISTINCT ord.IdOrderLocator) / COUNT(line.IdOrderLocator) AS OrdersOlderSeason,
    SUM(line.Units) AS TotalItemsOlderSeason,
    MAX(DATEDIFF(Day, zone.StartDate, DATEADD(year, -1, getDate()))) / COUNT(DISTINCT ord.IdOrderLocator) AS PurchaseFrequencyOlderSeason
FROM AR_NS_ORDERS AS ord
INNER JOIN AR_NS_ORDERS_LINE AS line
  ON ord.IdOrderLocator = line.IdOrderLocator
INNER JOIN AR_NS_ZONES AS zone
  ON line.Destination = zone.IdZone
WHERE ord.Status != 'ANNULLED'
  AND zone.Season = IIF(MAX(zone.EndDate) >= getDate(), YEAR(zone.EndDate)-1, YEAR(zone.EndDate))
  AND ord.CreatedDate BETWEEN zone.StartDate AND DATEADD(year, -1, getDate())
GROUP BY SubscriberKey

他抛出的错误是:

Error saving the Query field.An aggregate may not appear in the WHERE clause unless it is in a subquery contained in a HAVING clause or a select list, and the column being aggregated is an outer reference.

非常感谢

Answer 1

我认为您正在使用 SQL Server。如果我理解正确，您可以使用子查询来计算最大值并在 WHERE 子句中使用它:

SELECT SubscriberKey AS IdContact,
       SUM(ord.SalePrice) / COUNT(line.IdOrderLocator) AS SpentOlderSeason,
       COUNT(DISTINCT ord.IdOrderLocator) / COUNT(line.IdOrderLocator) AS OrdersOlderSeason,
       SUM(line.Units) AS TotalItemsOlderSeason,
       MAX(DATEDIFF(Day, zone.StartDate, DATEADD(year, -1, getDate()))) / COUNT(DISTINCT ord.IdOrderLocator) AS PurchaseFrequencyOlderSeason
FROM AR_NS_ORDERS ord JOIN
     AR_NS_ORDERS_LINE line
     ON ord.IdOrderLocator = line.IdOrderLocator JOIN
     (SELECT z.*, MAX(EndDate) OVER () as max_enddate
      FROM AR_NS_ZONES z
     ) zone
     ON line.Destination = zone.IdZone
WHERE ord.Status <> 'ANNULLED' AND
      zone.Season = (CASE WHEN max_EndDate >= getDate() THEN YEAR(zone.EndDate)-1 ELSE YEAR(zone.EndDate) END) AND
      ord.CreatedDate BETWEEN zone.StartDate AND DATEADD(year, -1, getDate())
GROUP BY SubscriberKey;