我有此SQL语句,并且SQL Server给我以下错误:
除非聚合不在HAVING子句或选择列表中包含的子查询中,否则它可能不会出现在WHERE子句中。
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, MIN(BillingDate),GETDATE()) OR (SUM(M1.InvoiceTotal)-SUM(M1.AmountApplied)) > M2.DelinquentAmountThreshold GROUP BY M1.BillingID , M2.Name , M2.DelinquentDaysThreshold , M2.DelinquentAmountThreshold
在where子句中,我只想提取最旧的未付款帐单日期大于DelinquentDaysThreshhold(以天为单位)或PastDueAmount(计算值)大于DelinquentAmountThreshold的记录。 由于某些原因,SQL Server不喜欢汇总的金额。
使用HAVING错误消息提示的,要求输入GROUP BY:
HAVING
GROUP BY
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, MIN(BillingDate),GETDATE()) GROUP BY M1.BillingID, M2.Name, M2.DelinquentDaysThreshold, M2.DelinquentAmountThreshold, DATEDIFF(d, MIN(BillingDate),GETDATE()) HAVING (SUM(M1.InvoiceTotal)-SUM(M1.AmountApplied)) > M2.DelinquentAmountThreshold