HQL查詢語句使用group by 子句進行分組查詢，使用having子句篩選分組結(jié)果。

按職位統(tǒng)計員工個數(shù)

 Long count=(Long)this.getCurrentSession().createQuery("select count(e.id) from Emp  e group by e.job").uniqueResult();

Hibernate:
select
count(emp0_.empNo) as col_0_0_
from
project.Emp emp0_
group by
emp0_.job

統(tǒng)計各個部門的平均工資

List<Object[]> avg=this.getCurrentSession().createQuery("select e.department.deptName,avg (e.salary) from Emp e group by e.department.deptName").list();

Hibernate:
select
department1_.deptName as col_0_0_,
avg(emp0_.salary) as col_1_0_
from
project.Emp emp0_,
project.Department department1_
where
emp0_.deptNo=department1_.deptNo
group by
department1_.deptName

統(tǒng)計各個職位的最低工資和最高工資

   List<Object[]> maxMin=this.getCurrentSession().createQuery("select e.job,max (e.salary),min (e.salary) from Emp e group by e.job").list();

Hibernate:
select
emp0_.job as col_0_0_,
max(emp0_.salary) as col_1_0_,
min(emp0_.salary) as col_2_0_
from
project.Emp emp0_
group by
emp0_.job

統(tǒng)計平均工資高于4000元的部門名稱，輸出部門名稱，部門平均工資

 List<Object[]> avg=this.getCurrentSession().createQuery("select e.department.deptName,avg (e.salary) from Emp e group by e.department.deptName having avg (e.salary)>4000").list();

Hibernate:
select
department1_.deptName as col_0_0_,
avg(emp0_.salary) as col_1_0_
from
project.Emp emp0_,
project.Department department1_
where
emp0_.deptNo=department1_.deptNo
group by
department1_.deptName
having
avg(emp0_.salary)>4000

使用select子句時,Hibernate返回的查詢結(jié)果為關(guān)系數(shù)據(jù)而不是持久化對象，不會占用Session緩存。為了方便訪問，可以定義一個JavaBean進行投影查詢來封裝查詢結(jié)果中的關(guān)系數(shù)據(jù)。