Sql Server + Mybatis-plus使用Page时报错

“OFFSET”附近有语法错误

(Sql Server + Mybatis-plus使用Page时报错：)

“OFFSET”附近有语法错误

最近开发遇到的问题，SqlServer+Mybatis-Plus Page插件进行分页时的报错
在这里插入图片描述

代码实现

Maven依赖

<!-- mybatis-plus -->
<dependency>
	<groupId>com.baomidou</groupId>
	<artifactId>mybatis-plus-boot-starter</artifactId>
	<version>${mybatis-plus.version}</version>
</dependency>

Mapper.xml

SELECT 
	zp1.[DESCRIPTION] AS [STATUS_NAME],
    ds.[STATUS_DATE],
    cu.[USER_NAME],
    ds.[REMARK]
FROM [DEPOSIT_STATUS] AS ds
    LEFT JOIN [ZA_P_1] AS zp1 ON zp1.[CODE]= ds.[STATUS_CODE]
    LEFT JOIN [CLIENT_USER] AS cu ON cu.[USER_ID]= ds.[CREATE_USERID]
WHERE
    ds.[DELETE_DATE] IS NULL
    AND ds.[DEPOSIT_ID] = #{vo.depositId,jdbcType=INTEGER}
ORDER BY ds.[LAST_COMMIT_DATE]

错误信息

Error querying database.  Cause: com.microsoft.sqlserver.jdbc.SQLServerException: 除非另外还指定了 TOP、OFFSET 或 FOR XML，否则，ORDER BY 子句在视图、内联函数、派生表、子查询和公用表表达式中无效。

解决过程

定位问题

找到报错的sql

SELECT 
	zp1.[DESCRIPTION] AS [STATUS_NAME],
    ds.[STATUS_DATE],
    cu.[USER_NAME],
    ds.[REMARK]
FROM [DEPOSIT_STATUS] AS ds
    LEFT JOIN [ZA_P_1] AS zp1 ON zp1.[CODE]= ds.[STATUS_CODE]
    LEFT JOIN [CLIENT_USER] AS cu ON cu.[USER_ID]= ds.[CREATE_USERID]
WHERE
    ds.[DELETE_DATE] IS NULL
    AND ds.[DEPOSIT_ID] = #{vo.depositId,jdbcType=INTEGER}
ORDER BY ds.[LAST_COMMIT_DATE]

控制显示的一句异常信息

WARN    at com.baomidou.mybatisplus.extension.plugins.PaginationInterceptor.concatOrderBy(PaginationInterceptor.java:142)- failed to concat orderBy from IPage, exception=null

进入com.baomidou.mybatisplus.extension.plugins.PaginationInterceptor.java,在concatOrderBy打上断点
在执行该句代码时异常：

Select selectStatement = (Select)CCJSqlParserUtil.parse(originalSql);

在控制台debug监视CCJSqlParserUtil.parse(originalSql)，直接抛出异常，是sql语句的问题

解决方案

去掉mapper.xml中sql的中括号

SELECT zp1.DESCRIPTION AS STATUS_NAME,
       ds.STATUS_DATE,
       cu.USER_NAME,
       ds.REMARK
FROM DEPOSIT_STATUS AS ds
         LEFT JOIN ZA_P_1 AS zp1 ON zp1.CODE= ds.STATUS_CODE
    AND TYPE= 11
         LEFT JOIN CLIENT_USER AS cu ON cu.USER_ID= ds.CREATE_USERID
WHERE
    ds.DELETE_DATE IS NULL
  AND ds.DEPOSIT_ID = #{vo.depositId,jdbcType=INTEGER}
  AND ds.CLIENT_ID = #{vo.clientId,jdbcType=INTEGER}
ORDER BY ds.LAST_COMMIT_DATE

去掉后成功运行，CCJSqlParserUtil.parse(originalSql)不报错。