InterSystems Cache聚合函数报错：WHERE子句不可使用聚合函数

这个报错是SQL的核心规则导致的——聚合函数（比如MAX()）不能直接放在WHERE子句里。原因很简单：WHERE是用来过滤原始行数据的，而聚合函数是对分组后的数据集进行计算，数据库在执行WHERE过滤的时候，还不知道分组后的聚合结果，自然没法用它来筛选原始行。

针对你的需求（找出AuditDate等于指定日期，且对应交易的最新日期早于该日期的记录），这里有两种可行的修改方案：

方案一：使用GROUP BY + HAVING子句 #

HAVING子句是专门用来过滤分组后的聚合结果的，刚好能解决你的问题。同时要注意LEFT JOIN的特性：如果某个Rent没有对应的BookTran记录，MAX(BookTran.TranDate)会返回NULL，需要把这种情况也纳入条件，避免漏掉数据。

修改后的代码：

Query dueToday(rToday As DType.Date) As %SQLQuery(ROWSPEC = "Book:User.Books,RefNo:%Integer") {
 SELECT BookODue.Rent, BookODue.RefNo 
 FROM BookODue 
 LEFT JOIN BookTran ON BookODue.Rent = BookTran.Rent 
 WHERE BookODue.AuditDate = :rToday
 GROUP BY BookODue.Rent, BookODue.RefNo
 HAVING MAX(BookTran.TranDate) < :rToday OR BookTran.TranDate IS NULL
}

方案二：用子查询预先计算聚合结果 #

如果BookTran表的数据量较大，这种方法可能性能更优：先通过子查询算出每个Rent对应的最新交易日期，再和BookODue关联过滤，避免对关联后的大结果集做聚合操作。

修改后的代码：

Query dueToday(rToday As DType.Date) As %SQLQuery(ROWSPEC = "Book:User.Books,RefNo:%Integer") {
 SELECT BookODue.Rent, BookODue.RefNo 
 FROM BookODue 
 LEFT JOIN (
     SELECT Rent, MAX(TranDate) AS MaxTranDate
     FROM BookTran
     GROUP BY Rent
 ) AS TranMax ON BookODue.Rent = TranMax.Rent
 WHERE BookODue.AuditDate = :rToday
 AND (TranMax.MaxTranDate < :rToday OR TranMax.MaxTranDate IS NULL)
}

额外注意事项 #

InterSystems Cache的SQL对GROUP BY的要求和标准SQL一致：SELECT子句中出现的非聚合列，必须全部出现在GROUP BY里（除非你调整了SQL模式，但严格模式下建议遵守这个规则）。另外，处理LEFT JOIN的NULL值是关键，否则会丢失那些没有交易记录的Rent条目。

内容的提问来源于stack exchange，提问作者CME