unless it is in a subquery contained in a HAVING clause or a select list.

sql查询报错：

An aggregate may not appear in the WHERE clause unless it is in a
subquery contained in a HAVING clause or a select list.

group 语句where子句使用Max函数或SUM等函数时会提示报错，需要将引where子句换成having

如：

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

会提示最上面的错误，调整成如下可通过：

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

参考：SQL查询汇总可能不会显示在WHERE子句中 https://codeday.me/bug/20181101/353176.html

巴特西

unless it is in a subquery contained in a HAVING clause or a select list.

最新文章

热门文章