Hibernate按月多group by场景：关联两个group by查询的HQL转换问题

核心思路：你的两个子查询都基于同一张documents表，仅统计过滤条件不同，可直接用条件聚合替代多子查询关联，完美避开HQL不支持FROM子句子查询的限制。

方法1：条件聚合编写HQL（推荐） #

无需关联子查询，一轮分组计算即可得到所有统计值，完全符合HQL语法规范：

SELECT 
    month(d.createdAt) AS m,
    (COUNT(CASE WHEN d.documentState = 'APPROVED' THEN 1 END) * 100.0) / 
    (COUNT(CASE WHEN d.documentState = 'APPROVED' THEN 1 END) + COUNT(CASE WHEN d.documentState = 'AUTHENTIC' THEN 1 END)) AS rate
FROM 
    Documents d
WHERE 
    d.documentState IN ('APPROVED', 'AUTHENTIC')
GROUP BY 
    month(d.createdAt)

注意：以上HQL是面向实体类编写，需将Documents、createdAt、documentState替换为你实际的实体类名和对应属性名。

方法2：Hibernate原生SQL查询 #

如果不想调整原SQL逻辑，可直接调用Hibernate原生SQL接口执行原有MySQL语句：

// Java调用示例
String originSql = "SELECT d.m,d.fn/(d.fn+d2.tp)*100 FROM \n" +
"(SELECT MONTH(created_at) AS m, COUNT(*) AS fn FROM documents WHERE document_state = 'APPROVED' GROUP BY m) d \n" +
"JOIN \n" +
"(SELECT MONTH(created_at) AS m, COUNT(*) AS tp FROM documents WHERE document_state = 'AUTHENTIC' GROUP BY m) d2 \n" +
"ON d.m = d2.m";
SQLQuery query = session.createSQLQuery(originSql);
// 可根据需求配置结果映射为DTO或直接接收对象数组
List<Object[]> resultList = query.list();

内容的提问来源于stack exchange，提问作者Shafiq Aiman