- 新手请教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子嵌套查询
[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。
