有这么个需求,排序项目编号后,再排序项目开始时间,再然后按照项目结束时间排序,具备组合优先排序的条件
开始解决问题前
先引入一个知识点,【整型和字符串的转换】 :
Oracle:TO_CHAR():
mysql :CAST(123 AS CHAR(3))
Oracle :TO_NUMBER():
mysql :cast( '123 ' as SIGNED INTEGER)
问题解决:
sql实现:
通用版:SELECT * FROM lc_project this_ WHERE 1=1 ORDER BY (projectNum+0) ASC, this_.startTime DESC, this_.endTime DESC
mysql版 :SELECT * FROM lc_project this_ WHERE 1=1 ORDER BY CAST(projectNum AS SIGNED INTEGER) ASC, this_.startTime DESC, this_.endTime DESC
oracle版:SELECT * FROM lc_project this_ WHERE 1=1 ORDER BY TO_NUMBER(projectNum) ASC, this_.startTime DESC, this_.endTime DESC
Hql解决问题版本:
@Override
public String doList() throws Exception {
pageModel.initModel(this);
DetachedCriteria criteria = DetachedCriteria.forClass(Project.class);
//criteria.addOrder(Order.desc("projectNum"));
//criteria.addOrder(Order.desc("startTime"));
//criteria.addOrder(Order.desc("endTime"));
//上边的排序用于varchar类型不可行,排序没有效果,为什么加个1=1,是引文sqlRestriction的criteria有个where在前边
criteria.add(Restrictions.sqlRestriction("1=1 ORDER BY (projectNum+0) asc ,startTime desc, endTime desc"));
baseService.getPageModel(pageModel, criteria);
return LIST;
}