分组查询

（1/5）添加分组字段：group by 子句

方法	等价HQL片段
groupBy(String fromClazzFieldName)	select fromClazz.fromClazzFieldName from... group by fromClazz.fromClazzFieldName
groupBy(String tableAlias, String fieldName, String alias)	select tableAlias.fieldName as alias from... group by tableAlias.fieldName

（2/5）添加组函数及过滤结果：聚合函数、having 子句

A、组函数

无 tableAlias 参数的重载方法，tableAlias=HqlHelper.currTable 即：fromClazz 的别名

distinct 仅当调用 countDistinct 方法时候添加。

方法	等价HQL片段
avg,count,countDistinct,max,min,sum(String tableAlias, String fieldName, String avgAlias)	select ..., sum,count,count,max,min,sum(distinct tableAlias.fieldName) as sumAlias from ... where ... group by ...

B、having 子句

having_XXXX 系列方法需要紧随组函数使用，表示对该组函数的过滤规则

方法	等价HQL片段
having_eq,ne,ge,gt,lt,le(Object value)	... group by ... having {current group function} =,!=,>=,>,<,<= value
having_between,notBetween(Object value)	... group by ... having {current group function} between,not between value1 and value2
having_in,notIn(Object... values)	... group by ... having {current group function} in,not in values
having_like,notLike(String value, MatchType matchType)	... group by ... having {current group function} like,not like matchType:value
having_ilike,notiLike(String value, MatchType matchType)	... group by ... having upper({current group function}) like,not like upper(matchType:value)
having_lengthEq,Ne,Ge,Gt,Lt,Le(Integer length)	... group by ... having length({current group function}) =,!=,>=,>,<,<= length

（3/5）过滤条件分组：having 子句的条件分组

 .having_and().条件1.条件2.having_end()  等价HQL片段 ： and (条件1 and 条件2) 
 .having_or().条件1.条件2.having_end()   等价HQL片段 ： or (条件1 and 条件2)

（4/5）分组排序

group_orderBy 方法需要紧随组函数使用，表示对该组函数的结果排序

方法	等价HQL片段
group_orderBy(OrderType orderType)	order by {current group function} orderType

（5/5）示例

    @Autowired
    private HqlHelperService helperService;

    @Test
    public void testHql() {
        HqlHelper helper = HqlHelper.queryFrom(City.class);
        // 示例一
        // 查询每个省份下的城市数量和最大的区号，返回城市数量 between 2 and 10 的数据
        helper.join(HqlHelper.currTable, "province", "p")
              .groupBy("p", "name", "provinceName")
              // 组函数及其结果cityCount的过滤规则
              .count("id", "cityCount")
                  .having_between(2L, 10L)
              .max("areaCode", "maxAreaCode");
        Records provinceCityCount = helperService.getRecordsGroup(helper);
        System.err.println("provinceCityCount1 =" + provinceCityCount);

        // 示例二
        // 在示例一的基础上
        //（1）去掉`吉林`省的记录
        //（2）过滤分组结果，只读取最大区号08和09开头的数据
        //（3）按照最大区号升序排列
        //（4）读取3条记录
        helper.resetQueryFrom(City.class)
              .join(HqlHelper.currTable, "province", "p")
              //（1）去掉`吉林`省的记录
              .ne("p", "name", "吉林")
              .groupBy("p", "name", "provinceName")
              // 组函数及其结果cityCount的过滤规则
              .count("id", "cityCount")
                  .having_between(2L, 10L)
              //（2）过滤分组结果，只读取最大区号08和09开头的数据
              .max("areaCode", "maxAreaCode")
                  .having_and()
                    .having_like("08", MatchType.START)
                    .having_or().having_like("09", MatchType.START).having_end()
                  .having_end()
                  //（3）按照最大区号升序排列
                  .group_orderBy(OrderType.asc)
              //（4）读取3条记录
              .setFirstResult(0).setMaxResults(3);
        provinceCityCount = helperService.getRecordsGroup(helper);
        System.err.println("provinceCityCount2 =" + provinceCityCount);
    }

//执行结果： 
    select
        province1_.name as col_0_0_,
        count(city0_.id) as col_1_0_,
        max(city0_.area_code) as col_2_0_ 
    from
        dodo_city city0_ 
    inner join
        dodo_province province1_ 
            on city0_.province_id=province1_.id 
    group by
        province1_.name 
    having
        count(city0_.id) between ? and ?

provinceCityCount1 =Records [rawData=[
    {maxAreaCode=0439, provinceName=吉林, cityCount=9}, 
    {maxAreaCode=0955, provinceName=宁夏, cityCount=5}, 
    {maxAreaCode=0898, provinceName=海南, cityCount=3}, 
    {maxAreaCode=0599, provinceName=福建, cityCount=9}, 
    {maxAreaCode=0897, provinceName=西藏, cityCount=7}, 
    {maxAreaCode=0859, provinceName=贵州, cityCount=9}, 
    {maxAreaCode=0919, provinceName=陕西, cityCount=10}, 
    {maxAreaCode=0979, provinceName=青海, cityCount=8}]]

    select
        province1_.name as col_0_0_,
        count(city0_.id) as col_1_0_,
        max(city0_.area_code) as col_2_0_ 
    from
        dodo_city city0_ 
    inner join
        dodo_province province1_ 
            on city0_.province_id=province1_.id 
    where
        province1_.name<>? 
    group by
        province1_.name 
    having
        (
            count(city0_.id) between ? and ?
        ) 
        and (
            max(city0_.area_code) like ? 
            or max(city0_.area_code) like ?
        ) 
    order by
        col_2_0_ asc limit ?

provinceCityCount2 =Records [rawData=[
    {maxAreaCode=0859, provinceName=贵州, cityCount=9}, 
    {maxAreaCode=0897, provinceName=西藏, cityCount=7}, 
    {maxAreaCode=0898, provinceName=海南, cityCount=3}]]

END