我有这个sql语句,sql Server给我以下错误：
An aggregate may not appear in the WHERE clause unless it is in a
subquery contained in a HAVING clause or a select list.
SELECT SUM(M1.InvoiceTotal)-SUM(M1.AmountApplied) as PastDueAmount,M1.BillingID,M2.Name,M2.DelinquentDaysThreshold,M2.DelinquentAmountThreshold,DATEDIFF(d,MIN(BillingDate),GETDATE()) as DaysLate FROM Invoices M1 LEFT JOIN ClientAccounts M2 ON M1.BillingID = M2.ID WHERE InvoiceTotal <> AmountApplied AND M2.DelinquentDaysThreshold > DATEDIFF(d,GETDATE()) OR (SUM(M1.InvoiceTotal)-SUM(M1.AmountApplied)) > M2.DelinquentAmountThreshold GROUP BY M1.BillingID,M2.DelinquentAmountThreshold
在where子句中,我只想拉出最旧的未付帐单发票日期大于DelinquentDaysThreshhold(以天为单位)的记录,或者PastDueAmount(计算值)大于DelinquentAmountThreshold.
由于某些原因sql Server不喜欢汇总金额.
解决方法
根据错误消息使用HAVING,该消息需要GROUP BY
SELECT
SUM(M1.InvoiceTotal)-SUM(M1.AmountApplied) as PastDueAmount,GETDATE()) as DaysLate
FROM
Invoices M1
LEFT JOIN
ClientAccounts M2 ON M1.BillingID = M2.ID
WHERE
InvoiceTotal <> AmountApplied
AND
M2.DelinquentDaysThreshold > DATEDIFF(d,GETDATE())
GROUP BY
M1.BillingID,GETDATE())
HAVING
(SUM(M1.InvoiceTotal)-SUM(M1.AmountApplied)) > M2.DelinquentAmountThreshold
