HQL 或 Criteria 中的 CASE 语句
源自 这个问题,是否可以对以下 SQL 语句使用 HQL 或 Criteria:
SELECT
e.type,
count(e),
count(d),
count (case when gender = 'male' then 1 else NULL end) AS NumberOfMaleEmployees
from Department d
JOIN d.employees e
WHERE e.dead = 'maybe'
GROUP BY e.type
虽然google 发现了一些结果,表明 HQL 支持 CASE 语句,Hibernate 3.6.6 失败并显示
QuerySyntaxException:意外标记:CASE
当我在 EntityManager 实例上创建上面的查询时出现这种情况。
为每个 e.type 创建另一个查询来手动确定男性数量(例如每个 e.type)是多么糟糕的主意,
SELECT
count(e),
from Department d
JOIN d.employees e
WHERE e.dead = 'maybe', e.type = ugly
因为可能有相当多的类型,这可能会很慢。我希望数据库能帮我完成这项工作。
derived from this question, is it possible to use HQL or Criteria for the following SQL statement:
SELECT
e.type,
count(e),
count(d),
count (case when gender = 'male' then 1 else NULL end) AS NumberOfMaleEmployees
from Department d
JOIN d.employees e
WHERE e.dead = 'maybe'
GROUP BY e.type
Although google comes up with a few hits that state HQL supports CASE statements, Hibernate 3.6.6 fails with a
QuerySyntaxException: unexpected token: CASE
when I create the query above on an EntityManager instance.
How much of a bad idea is it, to create another query for every e.type to determine the number of males manually, e.g. for every e.type
SELECT
count(e),
from Department d
JOIN d.employees e
WHERE e.dead = 'maybe', e.type = ugly
Since there could be quite a few types, this is potentially slow. I'd like the database to do the work for me.
嗯,看来支持 case 语句:
http: //docs.jboss.org/hibernate/core/3.5/reference/en/html/queryhql.html
它们似乎在 count() 中不起作用。另一种方法是使用 sum(case when...then 1 else 0 end) 而不是 < /a>
Well, it seems, case statements are supported:
http://docs.jboss.org/hibernate/core/3.5/reference/en/html/queryhql.html
They just don't seem to work within count(). An alternative would be using sum(case when... then 1 else 0 end) instead