gpt4 book ai didi

php - only_full_group_by : "ORDER BY clause is not in GROUP BY clause"

转载 作者:行者123 更新时间:2023-11-29 18:19:49 37 4
gpt4 key购买 nike

mysql新版本与only_full_group_by的问题

我需要使用 Group by (col_id) 和 order by (date) 获取表值我在 mysql 中的代码如下:

SELECT COUNT(created_date) AS `Rows` , ANY_VALUE(id) 
FROM `table` where `my_id` = 1 AND `status` = '0'
GROUP BY `id`
ORDER BY `created_date` DESC

我收到如下错误

SQLSTATE[42000]: Syntax error or access violation: 1055 Expression #1 of ORDER BY clause is not in GROUP BY clause and contains nonaggregated column 'database.table.created_date' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

谁能说出解决办法吗?

提前致谢...

最佳答案

使用“仅完整分组依据”时,order by 不能是任意列。 order by 是在 group by 之后解析的,因此只允许聚合键和聚合列。

您可以使用:

SELECT COUNT(created_date) AS `Rows`, ANY_VALUE(id)
FROM `table`
WHERE `my_id` = 1 AND `status` = '0'
GROUP BY `id`
ORDER BY ANY_VALUE(`created_date`) DESC;

如果我不得不猜测,你实际上想要MAX():

ORDER BY MAX(`created_date`) DESC;

关于php - only_full_group_by : "ORDER BY clause is not in GROUP BY clause",我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/46687582/

37 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com