MyBatis-Plus sql server 分页的时候查询语句有排序字段会出现 除非另外还指定了 TOP、OFFSET 或 FOR XML，否则，ORDER BY 子句在视图、内联函数、派生表、子查询和公用表表达式中无效。

当前使用版本 3.54

当前环境信息 例如: Java8 + sql server 2018

描述bug现象

SELECT COUNT(*) FROM (SELECT model.DM AS id,

FROM V_CUSTOMER model LEFT JOIN v_vipset v(nolock) ON model.dm=v.gkdm LEFT JOIN kehu(nolock) ON model.ckdm=kehu.khdm LEFT JOIN dianyuan AS d(nolock) ON v.JSDY=d.dydm WHERE 1=1 AND (model.DM like '%LD00000081%' OR model.GKMC like '%LD00000081%' OR model.SJ like '%LD00000081%') ORDER BY v.xfje ASC

) TOTAL

分页的时候增加排序字段会出现 除非另外还指定了 TOP、OFFSET 或 FOR XML，否则，ORDER BY 子句在视图、内联函数、派生表、子查询和公用表表达式中无效。

SELECT COUNT(*) FROM (SELECT model.DM AS id,

FROM V_CUSTOMER model LEFT JOIN v_vipset v(nolock) ON model.dm=v.gkdm LEFT JOIN kehu(nolock) ON model.ckdm=kehu.khdm LEFT JOIN dianyuan AS d(nolock) ON v.JSDY=d.dydm WHERE 1=1 AND (model.DM like '%LD00000081%' OR model.GKMC like '%LD00000081%' OR model.SJ like '%LD00000081%')

) TOTAL

去掉排序字段就好了

Comment From: cng1985

` public class SqlPaginationInnerInterceptor extends PaginationInnerInterceptor {

public SqlPaginationInnerInterceptor(DbType dbType) {
    super(dbType);
}

@Override
protected String lowLevelCountSql(String originalSql) {
    int index = originalSql.indexOf("order");
    if (index > 0) {
        String sql = originalSql.substring(0, index);
        return super.lowLevelCountSql(sql);
    }
    return super.lowLevelCountSql(originalSql);
}

}

`

折中办法

Comment From: nieqiurong

用格式提供完整的sql,

SELECT model.DM AS id,

FROM V_CUSTOMER model
LEFT JOIN v_vipset v(nolock) ON model.dm=v.gkdm
LEFT JOIN kehu(nolock) ON model.ckdm=kehu.khdm
LEFT JOIN dianyuan AS d(nolock) ON v.JSDY=d.dydm
WHERE 1=1
AND (model.DM like '%LD00000081%'
OR model.GKMC like '%LD00000081%'
OR model.SJ like '%LD00000081%')
ORDER BY v.xfje ASC

如果你的原始sql是这样的，那都无法执行，如果你后面还有些字段什么的，请提供解析错误的日志。