聚合可能不会出现在WHERE子句中,除非它在子查询中

我正在尝试使用以下代码：

m_Set.ClearQueryInfo();
m_Set.SetParameterWhere("PatID = @PatIDParam AND EffectiveEnrollmentDate IN (Select MAX(EffectiveEnrollmentDate))");
m_Set.SetWhere("PatID = ? AND EffectiveEnrollmentDate IN (Select MAX(EffectiveEnrollmentDate))");
m_Set.SetNumParams(1);
m_Set.SetParam("@PatIDParam", 1, PatIDParam.ToString());


但我最终收到以下错误：

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,

SELECT dbo.[PatRoster].* FROM

dbo.[PatRoster] WHERE PatID =

@PatIDParam AND

EffectiveEnrollmentDate IN (Select

MAX(EffectiveEnrollmentDate))

解决方法:

您的查询无效-选择MAX(EffectiveEnrollmentDate)未完成；它必须从该子查询中的某处选择“ EffectiveEnrollmentDate”.

另外,MAX()仅返回单个值,因此不需要IN-您可以直接进行比较操作符=.