SQL查询汇总可能不会显示在WHERE子句中

发布时间：2020-12-12 16:43:46 所属栏目：MsSql教程来源：网络整理

导读：我有这个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.Billing

我有这个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

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!