Hibernate---报表查询

• 报表查询用于对数据分组和统计, 与 SQL 一样, HQL 利用 GROUP BY 关键字对数据分组, 用 HAVING 关键字对分组数据设定约束条件.
• 在 HQL 查询语句中可以调用以下聚集函数

1. count():同级数据个数
2. min():求最小数
3. max():求最大数
4. sum():求和
5. avg():求平均数

1. 统计每个部门的人数

    @Test
    public void testGroupBy(){
        String hql = "SELECT COUNT(e) FROM Employee e GROUP BY e.dept";
        Query query = this.session.createQuery(hql);
        List list = query.list();
        System.out.println(list);
    }

• 使用HAVING 关键字筛选出部门人数大于10的部门

    @Test
    public void testGroupBy(){
        String hql = "SELECT COUNT(e) FROM Employee e GROUP BY e.dept HAVING COUNT(e) > 10";
        Query query = this.session.createQuery(hql);
        List list = query.list();
        System.out.println(list);
    }

• 计算雇员的平均工资,最高工资,最低工资

@Test
    public void testGroupBy(){
       // String hql = "SELECT COUNT(e) FROM Employee e GROUP BY e.dept HAVING COUNT(e) > 10";
        String hql = "SELECT AVG(e.salary),MAX(e.salary),MIN(e.salary) " +
                " FROM Employee e ";
        Query query = this.session.createQuery(hql);
        List<Object[]> list = query.list();
        for (Object[] o:list){
            System.out.println("平均工资: "+o[0]);
            System.out.println("最高工资: "+o[1]);
            System.out.println("最低工资: "+o[2]);
        }

    }

• 统计雇员工资总和

    @Test
    public void testGroupBy(){
        String hql = "SELECT SUM(e.salary) " +
              " FROM Employee e ";
        Query query = this.session.createQuery(hql);

        List<Object> list = query.list();
        for (Object o : list){
            System.out.println("工资总和 : " + (Double)o);
        }

    }