分组查询

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

方法等价HQL片段
groupBy(String fromClazzFieldName)select fromClazz.fromClazzFieldName
from…
group by fromClazz.fromClazzFieldName
groupByOther(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 子句的条件分组

  1.  .having_and().条件1.条件2.having_end()  等价HQL片段 ： and (条件1 and 条件2) 
  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）示例

  1.     @Autowired
  2.     private HqlHelperService helperService;
  4.     @Test
  5.     public void testHql() {
  6.         HqlHelper helper = HqlHelper.queryFrom(City.class);
  7.         // 示例一
  8.         // 查询每个省份下的城市数量和最大的区号，返回城市数量 between 2 and 10 的数据
  9.         helper.join(HqlHelper.currTable, "province", "p")
  10.               .groupByOther("p", "name", "provinceName")
  11.               // 组函数及其结果cityCount的过滤规则
  12.               .count("id", "cityCount")
  13.                   .having_between(2L, 10L)
  14.               .max("areaCode", "maxAreaCode");
  15.         Records provinceCityCount = helperService.getRecordsGroup(helper);
  16.         System.err.println("provinceCityCount1 =" + provinceCityCount);
  18.         // 示例二
  19.         // 在示例一的基础上
  20.         //（1）去掉`吉林`省的记录
  21.         //（2）过滤分组结果，只读取最大区号08和09开头的数据
  22.         //（3）按照最大区号升序排列
  23.         //（4）读取3条记录
  24.         helper.resetQueryFrom(City.class)
  25.               .join(HqlHelper.currTable, "province", "p")
  26.               //（1）去掉`吉林`省的记录
  27.               .ne("p", "name", "吉林")
  28.               .groupByOther("p", "name", "provinceName")
  29.               // 组函数及其结果cityCount的过滤规则
  30.               .count("id", "cityCount")
  31.                   .having_between(2L, 10L)
  32.               //（2）过滤分组结果，只读取最大区号08和09开头的数据
  33.               .max("areaCode", "maxAreaCode")
  34.                   .having_and()
  35.                     .having_like("08", MatchType.START)
  36.                     .having_or().having_like("09", MatchType.START).having_end()
  37.                   .having_end()
  38.                   //（3）按照最大区号升序排列
  39.                   .group_orderBy(OrderType.asc)
  40.               //（4）读取3条记录
  41.               .setFirstResult(0).setMaxResults(3);
  42.         provinceCityCount = helperService.getRecordsGroup(helper);
  43.         System.err.println("provinceCityCount2 =" + provinceCityCount);
  44.     }
  1. //执行结果： 
  2.     select
  3.         province1_.name as col_0_0_,
  4.         count(city0_.id) as col_1_0_,
  5.         max(city0_.area_code) as col_2_0_ 
  6.     from
  7.         dodo_city city0_ 
  8.     inner join
  9.         dodo_province province1_ 
  10.             on city0_.province_id=province1_.id 
  11.     group by
  12.         province1_.name 
  13.     having
  14.         count(city0_.id) between ? and ?
  16. provinceCityCount1 =Records [rawData=[
  17.     {maxAreaCode=0439, provinceName=吉林, cityCount=9}, 
  18.     {maxAreaCode=0955, provinceName=宁夏, cityCount=5}, 
  19.     {maxAreaCode=0898, provinceName=海南, cityCount=3}, 
  20.     {maxAreaCode=0599, provinceName=福建, cityCount=9}, 
  21.     {maxAreaCode=0897, provinceName=西藏, cityCount=7}, 
  22.     {maxAreaCode=0859, provinceName=贵州, cityCount=9}, 
  23.     {maxAreaCode=0919, provinceName=陕西, cityCount=10}, 
  24.     {maxAreaCode=0979, provinceName=青海, cityCount=8}]]
  26.     select
  27.         province1_.name as col_0_0_,
  28.         count(city0_.id) as col_1_0_,
  29.         max(city0_.area_code) as col_2_0_ 
  30.     from
  31.         dodo_city city0_ 
  32.     inner join
  33.         dodo_province province1_ 
  34.             on city0_.province_id=province1_.id 
  35.     where
  36.         province1_.name<>? 
  37.     group by
  38.         province1_.name 
  39.     having
  40.         (
  41.             count(city0_.id) between ? and ?
  42.         ) 
  43.         and (
  44.             max(city0_.area_code) like ? 
  45.             or max(city0_.area_code) like ?
  46.         ) 
  47.     order by
  48.         col_2_0_ asc limit ?
  50. provinceCityCount2 =Records [rawData=[
  51.     {maxAreaCode=0859, provinceName=贵州, cityCount=9}, 
  52.     {maxAreaCode=0897, provinceName=西藏, cityCount=7}, 
  53.     {maxAreaCode=0898, provinceName=海南, cityCount=3}]]

END