MyBatis-Plus SQL Server分页查询排序时异常

当前使用版本(必填,否则不予处理)

3.4.0

该问题是如何引起的？(确定最新版也有问题再提!!!)

SQL Server2008 分页时排序异常

重现步骤(如果有就写完整)

new QueryWrapper().lambda().orderByAsc(TABLE::getID) 生成的SQL SELECT COUNT() FROM (SELECT * FROM [TABLE] WHERE (ID= ?) ORDER BY ID ASC) TOTAL 若使用需要增加TOP, 增加select("TOP 100 PERCENT ") 后 SELECT COUNT(*) FROM (SELECT TOP 100 PERCENT * FROM [TABLE] WHERE (ID= ?) ORDER BY ID ASC) TOTAL 查询总条数正常，但查询列表数据异常，在 * 前边会多出来一个 TOP 100 PERCENT WITH selectTemp AS (SELECT TOP 100 PERCENT ROW_NUMBER() OVER (ORDER BY ID ASC) as row_number, TOP 100 PERCENT * FROM [TABLE] WHERE (ID= ?) ORDER BY ID ASC) SELECT * FROM selectTemp WHERE row_number BETWEEN 1 AND 10 ORDER BY row_number

报错信息

子查询内不允许使用order by语句

建议在查询总条数时去掉ORDER BY 部分。

Comment From: VampireAchao

是否存在IllegalSQLInnerInterceptor，或者尝试select中不进行子查询

Comment From: y88845601

没有使用 IllegalSQLInnerInterceptor，SELECT COUNT(*) FROM (SELECT * FROM [TABLE] WHERE (ID= ?) ORDER BY ID ASC) TOTAL 语句是page方法自动拼装的。

Comment From: qmdx

默认是对 count orderby 优化的， Server2008 无环境无法验证，欢迎解决 pr

Comment From: gobelief

我今天也遇到这个问题, 我将那个分页插件拷贝出来自己修改了一下, 临时解决了我的问题, 我改了两个地方 PaginationInnerInterceptor autoCountSql函数:

       if (CollectionUtils.isNotEmpty(orderBy)) {
                boolean canClean = true;
                if (groupBy != null) {
                    // 包含groupBy 不去除orderBy
                    canClean = false;
                }
                if (canClean) {
                    for (OrderByElement order : orderBy) {
                        // order by 里带参数,不去除order by
                        Expression expression = order.getExpression();
                        if (!(expression instanceof Column) && expression.toString().contains(StringPool.QUESTION_MARK)) {
                            canClean = false;
                            break;
                        }
                    }
                }
                if (canClean) {
                    plainSelect.setOrderByElements(null);
                }
            }

改为了

   if (CollectionUtils.isNotEmpty(orderBy)) {
                boolean canClean = true;

                    for (OrderByElement order : orderBy) {
                        // order by 里带参数,不去除order by
                        Expression expression = order.getExpression();
                        if (!(expression instanceof Column) && expression.toString().contains(StringPool.QUESTION_MARK)) {
                            canClean = false;
                            break;
                        }
                    }

                if (canClean) {
                    plainSelect.setOrderByElements(null);
                }
            }

第二个地方

  // 包含 distinct、groupBy不优化
            if (distinct != null || null != groupBy) {
                return lowLevelCountSql(select.toString());
            }

改为了

       // 包含 distinct、groupBy不优化
            if (distinct != null || null != groupBy) {
                return lowLevelCountSql(plainSelect.toString());
            }

Comment From: WangslVIP

今天遇到这个问题了，3.5.3版本，还没有修复....