新手请教hibernate中查询的问题

数据库中有资产表Asset,属性有type,name,price

表中记录:
type,name,price
1.software,oracle,10000
2.software,weblogic,20000
3.software,oracle,50000
4.hardware,computer,3000

要得到的统计结果:
类型,名称,数量,总价值
software,oracle,2,60000
software,weblogic,1,20000
hardware,computer,1,3000

请问在hibernate中怎么用hql查询?

select asset.type,asset.name,temp.namecount,temp.pricesum from Assets asset,

(select distinct asset.name name,count(*) namecount,sum(asset.price) pricesum from Assets asset

group by asset.name) temp

where asset.name=temp.name
这是我用的sql,但hibernate报错,据说hibernate不支持select from子嵌套查询

2个回答

[quote]你只查询了后面三列结果,我现在的问题是如何将第一列type也查询出来。
因为group by asset.name 前面不能查询asset.type,我不知道怎样做[/quote]

了解了,用:

[code="java"]String hql = "select asset.type as type, asset.name as name,count(asset.name) as namecount,sum(asset.price) as pricesum from Assets asset group by asset.name, asset.type";[/code]

[quote]String hql = "select distinct asset.name as name,count(asset.name) as namecount,sum(asset.price) as pricesum from Assets asset group by asset.name";[/quote]

试试看,其实hql和sql大同小异,字段指定别名时需要加as。

Csdn user default icon
上传中...
上传图片
插入图片
抄袭、复制答案,以达到刷声望分或其他目的的行为,在CSDN问答是严格禁止的,一经发现立刻封号。是时候展现真正的技术了!
立即提问