???????? 场景是一个后台的管理项目，突然要把数据库从oracle改成sqlserver，于是把表啊数据之类的，完全迁移到sqlserver之后，代码中的ibatis的写好的oracle的分页，在基于sqlsever数据库的系统上，不能运行，比如oracle里的dual表，rownum等，sqlserver里没有。
<select id="getLogList" resultClass="monitorLog" parameterClass="monitorLog"> SELECT * FROM (SELECT c.*, rownum r from (select LOG_ID logID, LOG_OPERTOR logOpertor, OPERTOR_TIME opertorTime, LOG_DESC logDesc from MONITOR_LOG a WHERE 1 = 1 <isNotEmpty property = "dateStart" prepend = "and" > to_char(a.OPERTOR_TIME,'yyyy-MM-dd hh24:mi:ss') >=#dateStart# and to_char(a.OPERTOR_TIME,'yyyy-MM-dd hh24:mi:ss') <=#dateEnd# </isNotEmpty> <isNotEmpty property = "logOpertor" prepend = "and" > a.LOG_OPERTOR =#logOpertor# </isNotEmpty> order by OPERTOR_TIME desc) c) where r >= #startRow# AND rownum <= #pageSize#
?以上是oracle的下，根据rownum来分页的，startRow和pageSize分别是实体类中对应的字段，开始行和页大小。
迁移到sqlserver之后，因为startRow和pageSize已经定了，所以修改的话，也得基于这两个字段。修改代码如下：
<select id="getLogList" resultClass="monitorLog" parameterClass="monitorLog"> SELECT TOP $pageSize$ LOG_ID logID, LOG_OPERTOR logOpertor, OPERTOR_TIME opertorTime, LOG_DESC logDesc from MONITOR_LOG a WHERE 1 = 1 <isNotEmpty property = "dateStart" prepend = "and" > CONVERT(varchar(100), a.OPERTOR_TIME, 23) >=#dateStart# and CONVERT(varchar(100), a.OPERTOR_TIME, 23) <=#dateEnd# </isNotEmpty> <isNotEmpty property = "logOpertor" prepend = "and" > a.LOG_OPERTOR =#logOpertor# </isNotEmpty> and LOG_ID>=( select max(LOG_ID) from ( select top $startRow$ LOG_ID from MONITOR_LOG order by LOG_ID ) as t ) order by LOG_ID </select>
?