2020-05-11 15:56
解决好了，重写一下此方法就行了。代码如下：
@Override
public String forPaginate(int pageNumber, int pageSize, StringBuilder findSql) {
int end = pageNumber * pageSize;
if (end <= 0) {
end = pageSize;
}
int begin = (pageNumber - 1) * pageSize;
if (begin < 0) {
begin = 0;
}
StringBuilder ret = new StringBuilder();
ret.append("SELECT * FROM ( SELECT row_number() over (order by tempcolumn) temprownumber, * FROM ");
if (findSql.toString().replace("distinct", "DISTINCT").indexOf("DISTINCT") == -1) {
ret.append(" ( SELECT TOP ").append(end).append(" tempcolumn=0,");
ret.append(findSql.toString().replaceFirst("(?i)select", ""));
} else {
ret.append(" ( SELECT DISTINCT TOP ").append(end).append(" tempcolumn=0,");
ret.append(findSql.toString().replace("distinct", "").replace("DISTINCT", "").replaceFirst("(?i)select", ""));
}
ret.append(")vip)mvp where temprownumber>").append(begin);
return ret.toString();
}
2020-05-11 14:55
@JFinal 我用的数据库是 sqlserver 2012，输出的sql语句是
SELECT
*
FROM
(
SELECT
row_number () OVER ( ORDER BY tempcolumn ) temprownumber, *
FROM
(
SELECT TOP
20 tempcolumn = 0,
DISTINCT project.*,
( SELECT name FROM syscode WHERE id = express.order_status ) AS orderStatus
FROM
operator,
project
LEFT JOIN express ON express.express_number = project.express_number
WHERE
project.isdeleted = 0
AND project.id = operator.operator_id
AND operator.operator_type = '9'
AND operator.operator_user_id = 22
AND project.status = 1
ORDER BY
project.id DESC
) vip
) mvp
WHERE
temprownumber > 0
，我发现这条语句的distinct是被放到了top的后面，所以才会报错。
在 jfinal 里是对我传入的findsql 进行了拼接。调用的是 SqlServerDialect 的 forPaginate 方法。里面的写法是
public String forPaginate(int pageNumber, int pageSize, StringBuilder findSql) {
int end = pageNumber * pageSize;
if (end <= 0) {
end = pageSize;
}
int begin = (pageNumber - 1) * pageSize;
if (begin < 0) {
begin = 0;
}
StringBuilder ret = new StringBuilder();
ret.append("SELECT * FROM ( SELECT row_number() over (order by tempcolumn) temprownumber, * FROM ");
// 这里没有判断当 findSql 里面有distinct 的情况
ret.append(" ( SELECT TOP ").append(end).append(" tempcolumn=0,");
ret.append(findSql.toString().replaceFirst("(?i)select", ""));
ret.append(")vip)mvp where temprownumber>").append(begin);
return ret.toString();
}
2020-05-10 17:12
@JFinal sql 在navicate里是可以运行的，是paginateByFullSql方法传的参数有问题吗？ 但我也是按文档上写的传参的啊？
