大家来拼这个hql语句(有分组，聚合)

Question

这有三张表，对应三个实体类表：chanage_cabinets(`ID` int,`APPLICATION_ID` int,`TYPE` varchar)
实体类：ChanageCabinets(Integer id,Integer applicationId,String type)表：chanage_apply(`ID` int,`OPERATION_ID` int,`PRODUCT_ID` int,`MRL` int)
实体类：ChanageApply(Integer id,Integer operationId,Integer productId,Integer mrl)表：chanage_cabinets_execute(`ID` int,,`APPLICATION_ID` int,`ATTITUDE` varchar)
实体类:ChanageCabinetsExecute(Integer id,Integer applicationId,String attitude)关联关系：chanage_cabinets和chanage_cabinets_execute的APPLICATION_ID都引用自chanage_apply的id
附上本地sql查询的语句select c.MRL,
c.OPERATION_ID,
c.PRODUCT_ID,
sum(if(d.TYPE='ONSHOW',1,0)) as onshowCount,
sum(if(d.TYPE='WITHDRAW',1,0)) as withdrawCount,
sum(if(d.TYPE='MOVE',1,0)) as moveCount,
sum(if(d.TYPE='WITHDRAW' and d.REVERSION='RETURN',1,0)) as reversionCount
from chanage_apply c,
chanage_cabinets d,
chanage_cabinets_execute e
where c.ID=d.APPLICATION_ID 
and c.ID=e.APPLICATION_ID 
and e.attitude='Y'
group by c.MRL,c.OPERATION_ID,c.PRODUCT_ID;
相信大家都能看懂以上语句
要求按chanage_apply表的MRL,OPERATION_ID,PRODUCT_ID依次分组，
并统计分组后chanage_cabinets表里面TYPE为'ONSHOW'时的数量，'WITHDRAW'时的数量，
'MOVE'时的数量，TYPE为'WITHDRAW'并且REVERSION为'RETURN'时的数量。也就是4个统计数值要用hibernate hql写，那些条件以及分组都好写，就是那4个统计数值不知道怎么写，
应该是用嵌套的hql语句
大家都来出出点子

Answer 1

直接用sql语句不就行了? hibernate又不是不支持sql语句!

Answer 2

顶
直接用SQL语句HIBERNATE两者都支持

Answer 3

hibernate 3支持case when，把if该成case when试试。

Answer 4

我当然知道hibernate支持sql，项目中现在我就是用的sql，不过领导说了，不要使用sql语句，要使用Hibernate hql来操作，
让系统与具体用什么数据库不要绑定在一起。
用hibernate不就是做到了与数据库低耦合吗？项目里除了hql查询就是DetachedCriteria查询，不用sql
只是现在我做的资产统计管理中一些统计查询语句不好写，我才先使用sql来实现功能，让页面先展现出来。让领导先看到我做出来了什么东西，
再去完善代码

Answer 5

14.11. group by子句
一个返回聚集值(aggregate values)的查询可以按照一个返回的类或组件（components)中的任何属性（property）进行分组： select cat.color, sum(cat.weight), count(cat) 
from Cat cat
group by cat.color
select foo.id, avg(name), max(name) 
from Foo foo join foo.names name
group by foo.id
having子句在这里也允许使用. select cat.color, sum(cat.weight), count(cat) 
from Cat cat
group by cat.color 
having cat.color in (eg.Color.TABBY, eg.Color.BLACK)如果底层的数据库支持的话(例如不能在MySQL中使用)，SQL的一般函数与聚集函数也可以出现 在having与order by 子句中。 select cat
from Cat cat
    join cat.kittens kitten
group by cat
having avg(kitten.weight) > 100
order by count(kitten) asc, sum(kitten.weight) desc注意group by子句与 order by子句中都不能包含算术表达式（arithmetic expressions）. 

Answer 6

规矩是人定的，为什么有简单的方法不去用呢？Hibernate 中根本就不支持这样的 HQL 语句。像统计之类的 SQL 语句在 Hibernate 中大都不支持。但是从执行效率来看建议采用原生 SQL 查询。虽然说起来 HQL 是独立于数据库系统的，但是我想一个项目的数据库很少会更改吧！如果你一定要坚持你领导所说的，那你就写多个所谓的 HQL 来代替这个 SQL 吧。

Answer 7

from chanage_apply c,
chanage_cabinets d,
chanage_cabinets_execute e
where c.ID=d.APPLICATION_ID 
and c.ID=e.APPLICATION_ID 
and e.attitude='Y'
像你这种采用内连接进行查询会比较慢，建议使用左外连接。

Answer 8

这里查询的数据不是哪个左表的数据能代表的，怎能用左外连接？？为什么需要写多个hql ？？
hibernate3中分组、聚合的语法也比较方便好用
使用case when then else endSELECT app.mrl,app.operationId,app.productId,
sum(case when cab.type='ONSHOW' then 1 else 0 end) as onshowNumber, 
sum(case when cab.type='WITHDRAW' then 1 else 0 end) as withdrawNumber, 
sum(case when cab.type='MOVE' then 1 else 0 end) as moveNumber,
sum(case when cab.type='WITHDRAW' and cab.reversion='RETURN' then 1 else 0 end) as returnNumber 
from ChanageApply app, ChanageCabinets cab,ChanageCabinetsExecute exe 
where app.id=cab.applicationId and app.id=exe.applicationId 
and app.mrl!=null and app.mrl!=0 
and app.operationId!=null and app.operationId!=0 
and app.productId!=null and app.productId!=0 
and exe.attitude='Y' 
group by app.mrl,app.operationId,app.productId 

Answer 9

丢100分，结果还是自己找到答案。呀！
不过是7楼提到case when我才知道hibernate3中还有这种语法
问题早已解决。
没人给出满意答案
遗憾结贴！

Answer 10

哎，那随便你吧，有些统计查询的 SQL 根本不可能使用 HQL 来替换的。再者就算你这个使用了 case when，但是交叉连接，也就是 FROM 后面跟了三张表，这样会产生笛卡尔积，查询效率严重下降，
可以使用 LEFT JOIN 来提高查询速度。但是 Hibernate 中如果在实体映射中没有关联是不能使用 LEFT JOIN 的。该说的都已经说完了。希望楼主不要强求在 HQL 上。