sql - 在 MySQL 语句中使用 datediff

Question

这是我使用 datediff 的 SQL 语句:

SELECT SUM(b134_nettpay) AS Total, b134_rmcid, b134_recdate 
FROM  B134HREC 
WHERE datediff (dd, '2006-05-05', getdate()) > 90
GROUP BY b134_rmcid, b134_recdate 
ORDER BY b134_recdate DESC, b134_rmcid

我需要像这样用 MAX(b134_recdate) 替换硬编码日期“2006-05-05”:

SELECT SUM(b134_nettpay) AS Total, b134_rmcid, b134_recdate 
FROM  B134HREC 
where datediff (dd, MAX(b134_recdate), getdate()) > 90
GROUP BY b134_rmcid, b134_recdate 
ORDER BY b134_recdate DESC, b134_rmcid

但我收到此错误消息:

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.

知道如何修复我的 SQL 语句吗？

Answer 1

尝试

SELECT SUM(b134_nettpay) AS Total, b134_rmcid, b134_recdate 
FROM  B134HRE 
where datediff (dd, 
      (select MAX(b134_recdate) from B134HRE)
      , getdate()) > 90
GROUP BY b134_rmcid, b134_recdate 
ORDER BY b134_recdate DESC, b134_rmcid