sql server-用hibernate實現sqlserver分頁的問題

用hibernate實現sqlserver分頁的問題

下面這段偽代碼，當顯示第一頁的時候正常，但是第二頁開始就會報錯了，錯誤是：
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();

最佳回答：

這個需求有個問題：

1. 先解決你的問題，sql1 union sql2 order by xx沒有意義，除非是(sql1 union sql2) order by xx ,你想想

2. join合並查詢，還有Union聯合查詢，這種sql怎麼優化，cost都不會太低，對這個，你還要分頁！！建議重構邏輯，生成結果表，在頁面裡簡單分頁查詢。