HQL 或 Criteria 中的 CASE 语句

发布于 11-29 19:42 字数 773 浏览 0 评论 0原文

源自 这个问题,是否可以对以下 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.

如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。

扫码二维码加入Web技术交流群

发布评论

需要 登录 才能够评论, 你可以免费 注册 一个本站的账号。

评论(1

紧拥背影2024-12-06 19:42:49

嗯,看来支持 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

~没有更多了~
我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站,即表示您同意使用 Cookies 和您的相关数据。
原文