sql-server - SQL - HAVING 子句中的聚合

Question

我知道这个问题已经被问了很多次，但是当我解决错误消息并使用 HAVING 子句时，我仍然收到可怕的:

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
mr.ClubKeyNumber,
COUNT(mr.MonthlyReportID),
SUM(CONVERT(int,mr.Submitted))
FROM MonthlyReport mr
WHERE mr.ReportYear = 2014
AND COUNT(mr.MonthlyReportID) = 12
GROUP BY mr.ClubKeyNumber
HAVING (SUM(CONVERT(int,mr.Submitted))) > 11

Answer 1

问题不在于您的 HAVING 子句，而在于您的 WHERE 子句。

您的 where 子句中有一个聚合 count，请尝试以下操作:

SELECT
mr.ClubKeyNumber,
COUNT(mr.MonthlyReportID),
SUM(CONVERT(int,mr.Submitted))
FROM MonthlyReport mr
WHERE mr.ReportYear = 2014
GROUP BY mr.ClubKeyNumber
HAVING (SUM(CONVERT(int,mr.Submitted))) > 11 and COUNT(mr.MonthlyReportID) = 12

where 子句检查 group by 子句之前聚合的每一行。在group by之后之前，它无法计算您的MonthlyReportID，因此请将其移至having子句.

Here is a simple example你可以用它来展示 vs 的位置。