在 MySQL 语句中使用 datediff
这是我使用 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
但我收到此错误消息:
聚合不能出现在 WHERE 子句中,除非它位于 HAVING 子句或选择列表中包含的子查询中,并且聚合的列是外部引用。
知道如何修复我的 SQL 语句吗?
This is my SQL statement that works using datediff:
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
I need to Replace the Hardcoded date '2006-05-05' with MAX(b134_recdate) like so:
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
But i get this Error message:
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.
Any idea how to fix my SQL Statement?
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(3)
尝试
Try
您是否尝试过移动此:
datediff (dd, MAX(b134_recdate), getdate()) > > 90
到 HAVING 子句?
sql 语句为:
<代码>
选择 SUM(b134_nettpay) AS 总计、b134_rmcid、b134_recdate
来自 B134HRE
按 b134_rmcid、b134_recdate 分组
按 b134_recdate DESC、b134_rmcid 排序
有 datediff (dd, MAX(b134_recdate), getdate()) > 90
Have you tried moving this:
datediff (dd, MAX(b134_recdate), getdate()) > 90
to a HAVING clause?
The sql statement would be:
SELECT SUM(b134_nettpay) AS Total, b134_rmcid, b134_recdate
FROM B134HRE
GROUP BY b134_rmcid, b134_recdate
ORDER BY b134_recdate DESC, b134_rmcid
HAVING datediff (dd, MAX(b134_recdate), getdate()) > 90
这段代码适用于 sql server
我不确定 MAX 是否适用于整个表:
或每个组:
但你可以尝试一下
This code is for sql server
I'm not sure if the MAX sould be for the entire table:
or per each group:
but you could give these a try