关于一个DB2 order by排序的问题
SQL异常信息：00000023 SystemOut O - 在 SELECT 子句、HAVING 子句 或 ORDER BY 子句中指定的以 "ID" 开始的表达式未在 GROUP BY 子句中指定，
或者它在 SELECT 子句、HAVING 子句或 ORDER BY 子句中，具有列函数，但未指定 GROUP BY 子句。. SQLCODE=-119, SQLSTATE=42803, DRIVER=3.62.56
Java代码：
public ItemPage getAlarmInfoPage(String systemId, String ipAddr, int pageNo, int pageSize) {
String queryHql = "from AlarmInfo t where 1=1 ";
if(systemId != null && !"".equals(systemId.trim())){
queryHql += "and t.systemId "+LikeEscape.likeEscapeParam(systemId);
}
if(ipAddr != null && !"".equals(ipAddr.trim())){
queryHql += "and t.ipAddr "+LikeEscape.likeEscapeParam(ipAddr);
}
queryHql += " ORDER BY t.id";
String countHql = "select count(*) "+queryHql;
return query(queryHql, countHql, pageNo==0?1:pageNo, pageSize==0?10:pageSize);
}
分析：
以上期望是分页查询AlarmInfo的记录，并且查询结果根据ID来进行排序，在Hibernate中生成的DB2方言为：
0000002a SystemOut O Hibernate:
select
*
from
( select
rownumber() over(
order by
alarminfo0_.ID) as rownumber_,
alarminfo0_.ID as ID0_,
alarminfo0_.ENTI_ID as ENTI2_0_,
alarminfo0_.ENTI_CODE as ENTI3_0_,
alarminfo0_.ENTI_NAME as ENTI4_0_,
alarminfo0_.KPI_ID as KPI5_0_,
alarminfo0_.KPI_CODE as KPI6_0_,
alarminfo0_.KPI_NAME as KPI7_0_,
alarminfo0_.PROV_CODE as PROV8_0_,
alarminfo0_.CITY_CODE as CITY9_0_,
alarminfo0_.SYSTEM_ID as SYSTEM10_0_,
alarminfo0_.CLASS_ID as CLASS11_0_,
alarminfo0_.SCLASS_ID as SCLASS12_0_,
alarminfo0_.BMODULE_ID as BMODULE13_0_,
alarminfo0_.HOUSE_ID as HOUSE14_0_,
alarminfo0_.IP_ADDR as IP15_0_,
alarminfo0_.VALUE as VALUE0_,
alarminfo0_.ALERT_TIMES as ALERT17_0_,
alarminfo0_.ALAR_LEVEL as ALAR18_0_,
alarminfo0_.RETRIVE_DATE as RETRIVE19_0_,
alarminfo0_.FIRST_DATE as FIRST20_0_,
alarminfo0_.LAST_DATE as LAST21_0_,
alarminfo0_.STATUS as STATUS0_,
alarminfo0_.LAST_UPD as LAST23_0_,
alarminfo0_.ALAR_DESC as ALAR24_0_
from
ALARM_INFO alarminfo0_
where
1=1
order by
alarminfo0_.ID ) as temp_
where
rownumber_ <= ?
0000002a SystemOut O Hibernate:
select
count(*) as col_0_0_
from
ALARM_INFO alarminfo0_
where
1=1
order by alarminfo0_.ID
我们先了解下DB2的SELECT语句的执行顺序：form子句组装来自不同数据源的数据->where子句基于指定的条件对记录进行筛选->group by子句将数据划分为多个分组->使用聚集函数进行计算->使用having子句对分组进行筛选->计算所有的表达式->使用order by对结果集进行排序。
在看SQL异常信息：在 SELECT 子句、HAVING 子句 或 ORDER BY 子句中指定的以 "ID" 开始的表达式未在 GROUP BY 子句中指定，或者它在 SELECT 子句、HAVING 子句或 ORDER BY 子句中，具有列函数，但未指定 GROUP BY 子句。也就是说在对结果集执行order by排序操作时找不到ID这个元素。
代码只需要稍微修改下即可,修改后的代码：
public ItemPage getAlarmInfoPage(String systemId, StringipAddr, int pageNo, int pageSize) {
String queryHql = "from AlarmInfo t where 1=1 ";
if(systemId != null &&!"".equals(systemId.trim())){
queryHql += "and t.systemId"+LikeEscape.likeEscapeParam(systemId);
}
if(ipAddr != null && !"".equals(ipAddr.trim())){
queryHql += "and t.ipAddr"+LikeEscape.likeEscapeParam(ipAddr);
}
String countHql = "select count(*) "+queryHql;
queryHql += " ORDER BY t.id";
return query(queryHql, countHql, pageNo==0?1:pageNo, pageSize==0?10:pageSize);
}
页:
[1]