聚合函数
Group by
Having
聚合函数:查询结果作为long类型返回
Count() |
统计符合条件的记录条数 |
Avg() |
求平均值 |
Sum() |
求和 |
Max() |
求最大值 |
Min() |
求最小值 |
@Test
public void select10(){
Transactiontx=null;
Sessionsession=null;
try{
session=HibernateUtils.getSession();
tx=session.beginTransaction();
Stringhql="select min(s.age) from Student s"; //count,max..
Queryquery=session.createQuery(hql);
Listlist=query.list(); // list在底层是使用动态的数组保存数据的
System.out.println(list.get(0));
tx.commit();
}catch(HibernateExceptionhe){
if(tx!=null){
tx.rollback();
}
he.printStackTrace();
}finally{
HibernateUtils.closeSession(session);
}
}
Group by:对查询的数据,根据条件进行分组。
Groupby 经常跟聚合函数一起使用
"SELECTs.grade , COUNT(*) FROM Student AS s GROUP BY s.grade"
@Test
public void select11(){
Transactiontx=null;
Sessionsession=null;
try{
session=HibernateUtils.getSession();
tx=session.beginTransaction();
Stringhql="select count(s.id),s.clazz from Student s group by s.clazz";
Queryquery=session.createQuery(hql);
List<Object[]>list=query.list();
for(Object[]objs:list){
for(Objectobj:objs){
System.out.println(obj);
}
}
tx.commit();
}catch(HibernateExceptionhe){
if(tx!=null){
tx.rollback();
}
he.printStackTrace();
}finally{
HibernateUtils.closeSession(session);
}
}
Having
having关键字和group by关键字搭配使用,它对分组后的记录进行筛选
"SELECT s.grade FROM Student AS s GROUP BY s.grade HAVING COUNT(s.id) >20"
对select11()修改:
Stringhql="select count(s.id),s.clazz from Student s group by s.clazz having avg(s.age)>20";