按月休眠多个组

Question

我想加入2个group by查询。mysql的查询是这样的:

SELECT d.m,d.fn/(d.fn+d2.tp)*100 FROM 
(
SELECT MONTH(created_at) AS m, COUNT(*) AS fn 
FROM documents 
WHERE 
document_state = "APPROVED" 
GROUP BY m
) d 
JOIN 
(
SELECT MONTH(created_at) AS m, COUNT(*) AS tp 
FROM documents 
WHERE 
document_state = "AUTHENTIC" 
GROUP BY m
) d2 
ON d.m = d2.m

我无法转换为HQL，因为hibernate不支持from中的子查询。

Answer 1

你可以使用一个本地查询，但是这个查询很容易被重新定义。

SELECT 
MONTH(created_at), 
COUNT(CASE WHEN document_state = "APPROVED" THEN 1 ELSE NULL END)
/ (
COUNT(CASE WHEN document_state = "APPROVED" THEN 1 ELSE NULL END)
+ COUNT(CASE WHEN document_state = "AUTHENTIC" THEN 1 ELSE NULL END)
) * 100
FROM documents
GROUP BY MONTH(created_at)

所以HQL可能:

SELECT 
MONTH(d.createdAt), 
COUNT(CASE WHEN d.state = "APPROVED" THEN 1 ELSE NULL END)
/ (
COUNT(CASE WHEN d.state = "APPROVED" THEN 1 ELSE NULL END)
+ COUNT(CASE WHEN d.state = "AUTHENTIC" THEN 1 ELSE NULL END)
) * 100
FROM Document d
GROUP BY MONTH(d.createdAt)