用hibernate实现sqlserver分页的问题

Question

下面这段伪代码，当显示第一页的时候正常，但是第二页开始就会报错了，错误是：
org.hibernate.exception.SQLGrammarException: could not extract ResultSet
Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: 除非另外还指定了 TOP、OFFSET 或 FOR XML，否则，ORDER BY 子句在视图、内联函数、派生表、子查询和公用表表达式中无效。

 String sql="select sd.sensorInputDataID as 数据ID,e.equipmentName as 设备名,sd.date as 日期  "+  
                "from SensorInputData as sd left join Sensor as s on sd.sensorId=s.sensorId left join EquipmentMessage as e on s.equipmentId=e.equipmentId "+ 
                "where sd.date between '2015/08/13 00:00:00' and '2015/08/13 23:59:59'  and e.equipmentName like'%设备5%' union "+ 

                "select sd.sensorInputDataID as 数据ID,e.equipmentName as 设备名,sd.date as 日期  "+  
                "from SensorInputData as sd left join Sensor as s on sd.sensorId=s.sensorId left join EquipmentMessage as e on s.equipmentId=e.equipmentId "+ 
                "where sd.date between '2015/08/13 00:00:00' and '2015/08/13 23:59:59'  and e.area like'%设备5%' order by sd.date asc";

                session.createSQLQuery(sql).setFirstResult((pageNow-1)*pageSize).setMaxResults(pageSizeInt).list();

Answer 1

这个需求有个问题：

1. 先解决你的问题，sql1 union sql2 order by xx没有意义，除非是(sql1 union sql2) order by xx ,你想想

2. join合并查询，还有Union联合查询，这种sql怎么优化，cost都不会太低，对这个，你还要分页！！建议重构逻辑，生成结果表，在页面里简单分页查询。