HQL或条件中的CASE语句 [英] CASE statement in HQL or Criteria

问题描述

源自此问题，是否可以将HQL或条件用于以下SQL语句：

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

尽管google提出了一些建议HQL支持CASE语句，Hibernate 3.6.6失败，并出现

Although google comes up with a few hits that state HQL supports CASE statements, Hibernate 3.6.6 fails with a

QuerySyntaxException：意外令牌：CASE

QuerySyntaxException: unexpected token: CASE

。

创建多少个坏主意针对每个e.type的另一个查询，以手动确定男性人数，例如对于每个e.type

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语句：

Well, it seems, case statements are supported:

http://docs.jboss.org/hibernate/core/3.5/reference/en/html/queryhql.html

他们只是不似乎在count（）中工作。另一种选择是使用sum（如果...则为1，否则为0结束）代替

They just don't seem to work within count(). An alternative would be using sum(case when... then 1 else 0 end) instead

这篇关于HQL或条件中的CASE语句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！