MySQL 错误 : Non-grouping field is used in HAVING clause

Question

有没有办法更正此查询，使其在启用 ONLY_FULL_GROUP_BY 的情况下工作？

SELECT LOWER(s) AS lower_s, SUM(i) AS sum_i
FROM t
GROUP BY 1
HAVING LENGTH(lower_s) < 5

给出错误信息

Non-grouping field 'lower_s' is used in HAVING clause

Fiddle

Answer 1

你为什么不直接使用 where LENGTH(LOWER(s)) < 5

似乎使用了having不在这里。

根据 having sql wiki

A HAVING clause in SQL specifies that an SQL SELECT statement should only return rows where aggregate values meet the specified conditions. It was added to the SQL language because the WHERE keyword could not be used with aggregate functions.[1]