mysql - MySQL使用max()无效使用组函数

我试图选择一个boolean值，其中order.lastUpdated早于30分钟前，但出现错误：
SQL状态[HY000]；错误代码[1111]；无效使用组函数；
嵌套异常是java.sql.sql exception:组的使用无效
功能
以下是查询：

select
c.externalReference channelReference,
c.id channelId,
max(o.lastUpdated) lastUpdated,
sum(if(max(o.lastUpdated) < date_sub(now(), interval 30 minute), 0, 1)) beforeThreshold
from channel c
join order_item o on c.id = o.channelId
where date(o.lastUpdated) = date(now())
and o.lastUpdated > date_sub(now(), interval 1 hour)
group by c.externalReference;

如果o.lastUpdated早于30分钟前，我必须使用max()，如何返回布尔值？

最佳答案

不能嵌套像SUM()和MAX()这样的聚合函数。您需要在子查询中执行内部查询。

SELECT c.externalReference AS channelReference,
       c.id AS ChannelId
       o.lastUpdated,
       SUM(IF(o.lastUpdated < DATE_SUB(NOW(), INTERVAL 30 MINUTE), 0, 1) AS beforeThreshold
FROM channel AS c
JOIN (SELECT channelId, MAX(lastUpdated) AS lastUpdated
      FROM order_item
      WHERE DATE(lastUpdated) = TODAY()
        AND lastUpdated > DATE_SUB(NOW(), INTERVAL 1 HOUR)
      GROUP BY channelId) AS o
ON c.id = o.channelId
GROUP BY c.externalReference

关于mysql - MySQL使用max()无效使用组函数，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/39086549/