public List getAllRegionRateStatistics(
final List<CodeNameMapBean> regions_CodeNameBean,
final Date beginDate, final Date endDate, final String seq) {
return getHibernateTemplate().executeFind(new HibernateCallback() {
public Object doInHibernate(org.hibernate.Session session)
throws HibernateException {
String sql = "";
if (regions_CodeNameBean == null
|| regions_CodeNameBean.size() == 0) {
return null;
} else {
for (int i = 0; i < regions_CodeNameBean.size(); i++) {
sql = sql
+ " or rg.regionId="
+ ((CodeNameMapBean) regions_CodeNameBean
.get(i)).getCodeId();
}
Query query = session
.createSQLQuery("select EStat_Sub_PStat.stid stid,sum(up) up,sum(dn) dn,sum(updn) updn,sum(allc) allc from "
+ " (SELECT rg.regionId AS stid,"
+ "sum(st.upCount) AS up,"
+ "sum(st.dnCount) AS dn,"
+ "sum(st.updnCount) AS updn,"
+ "min(p.finishedCount) AS allc"
+ " from T_EquipmentRateStatistic st,T_Subregion rg,T_Portstatistic p"
+ " where st.subregionId=rg.subregionId "
+ sql.replaceFirst(" or ", "and (")
+ ")"
+ " and p.subregionId=rg.subregionId"
+ " and p.updateTime>=:startUpTime and p.updateTime<:endUpTime "
+ " and st.updateTime>=:start_uptime and st.updateTime<:end_uptime "
+ " group by rg.regionId,st.subregionid) EStat_Sub_PStat"
+ " group by EStat_Sub_PStat.stid").addScalar("STID", Hibernate.LONG).addScalar("UP", Hibernate.LONG)
.addScalar("DN", Hibernate.LONG).addScalar("UPDN", Hibernate.LONG).addScalar("ALLC", Hibernate.LONG);
// + " order by sum(st."
// + seq + ")/sum(st.allCount)");
query.setParameter("startUpTime", new Timestamp(beginDate
.getTime()));
query.setParameter("endUpTime", new Timestamp(endDate
.getTime()));
query.setParameter("start_uptime", new Timestamp(beginDate
.getTime()));
query.setParameter("end_uptime", new Timestamp(endDate
.getTime()));
return query.list();
}
}
});
}
如果最外面的一层:select EStat_Sub_PStat.stid stid,sum(up) up,sum(dn) dn,sum(updn) updn,sum(allc) allc from。。。。sum(up)这些不取别名的话,后面如果用到了addScalar("UP", Hibernate.LONG)这些的时候,会报一个java.sql.SQLException: 列名无效。如果sum(up)这些不取别名,并且不用addScalar("UP", Hibernate.LONG)这些的时候,该查询只会取到第一个字段EStat_Sub_PStat.stid。