java - 如何在HQL中选择group by LEFT(t.field, 6)？

Question

干净的 SQL 语法 (MySQL) 允许使用“group by LEFT(...)”进行选择示例:

SELECT * FROM `table` t  
WHERE t.field1 = 777 AND t.field2 = 0 
GROUP BY LEFT(t.field3, 6) 
HAVING COUNT(*) = 4;

但是如果我尝试使用 HQL:

" select t  from TableEntity t " +
" where t.field1 = :field_1 and t.field2 = :field_2 " +
" group by LEFT(t.field3, 6) " +
" having COUNT(*) = :other_condition "

我有一个异常(exception) org.hibernate.hql.ast.QuerySyntaxException:意外标记:第 1 行第 12 列附近的 LEFT [

这种情况下可以使用HQL吗？

Answer 1

我认为你不能做你想做的事，因为MySQL扩展了group by——其他数据库通常不支持这种扩展。你可以这样做:

SELECT substring(t.field3, 1, 6), count(*) as cnt
FROM `table` t  
WHERE t.field1 = 777 AND t.field2 = 0 
GROUP BY substring(t.field3, 1, 6)
HAVING COUNT(*) = 4;

请注意，group by 中未聚合的列也在 select 中。