Mybatis-PageHelper
Mybatis-PageHelper copied to clipboard
pagehelper
关于order by 排序查询出来结果不对的问题
- [ ] 我已在 issues 搜索类似问题，并且不存在相同的问题.
异常模板
使用环境
- PageHelper 版本: 5.1.11
- 数据库类型和版本: sqlserver
- JDBC_URL: xxx
SQL 解析错误
分页参数
PageHelper.startPage(1,35).doSelectPageInfo(() -> xxx)
原 SQL
SELECT
*
FROM
(
SELECT
snf.ID,
snf.CorpID,
snf.ProductID,
snf.Spell,
snf.Logo,
snf.Name,
snf.Description,
snf.Recommend,
snf.Province,
snf.City,
snf.Address,
snf.IsSelf,
snf.PriceTrend,
snf.Tags,
snf.Lal,
snf.Sort,
iif (
business.factoryID IS NOT NULL
AND snf.Status = 'normal',
'business',
snf.Status
) AS status
FROM
SalesNoteFactory snf
LEFT JOIN (
SELECT DISTINCT
factoryID
FROM
FactoryLastData
WHERE
PriceStr != ''
AND PriceStr != '停收'
AND PriceStr IS NOT NULL
AND AvgPrice IS NOT NULL
AND AvgPrice != ''
) business ON snf.ID = business.factoryID
) f
WHERE
f.Province IN (?, ?)
ORDER BY
CHARINDEX(
',' + f.Province + ',',
',' +?+ ','
) DESC,
f.City,
f.Sort,
f.ID
分页SQL：
SELECT
TOP 35 *
FROM
(
SELECT
ROW_NUMBER () OVER (
ORDER BY
ROW_ALIAS_1 DESC,
City,
Sort,
ID
) PAGE_ROW_NUMBER,
*
FROM
(
SELECT
*, CHARINDEX(
',' + f.Province + ',',
',' + ? + ','
) AS ROW_ALIAS_1
FROM
(
SELECT
snf.ID,
snf.CorpID,
snf.ProductID,
snf.Spell,
snf.Logo,
snf.Name,
snf.Description,
snf.Recommend,
snf.Province,
snf.City,
snf.Address,
snf.IsSelf,
snf.PriceTrend,
snf.Tags,
snf.Lal,
snf.Sort,
iif (
business.factoryID IS NOT NULL
AND snf.Status = 'normal',
'business',
snf.Status
) AS status
FROM
SalesNoteFactory snf
LEFT JOIN (
SELECT DISTINCT
factoryID
FROM
FactoryLastData
WHERE
PriceStr != ''
AND PriceStr != '停收'
AND PriceStr IS NOT NULL
AND AvgPrice IS NOT NULL
AND AvgPrice != ''
) business ON snf.ID = business.factoryID
) f
WHERE
f.Province IN (?, ?)
) AS PAGE_TABLE_ALIAS
) AS PAGE_TABLE_ALIAS
WHERE
PAGE_ROW_NUMBER > 0
ORDER BY
PAGE_ROW_NUMBER
异常描述
比对上述sql会发现排序的参数被前置到查询列那边了
如果我传入广西,江苏期望应该是： 0-> 广西 ,1-> 江苏, 2->广西,江苏
实际上却是顺序由于从 未解析前的 0,1,2 变成了解析后的2,0,1 ： 2->广西 0-> 江苏 ,1-> 广西,江苏
导致查询结果不正确这种情况只剩下自己写分页这一条路了么？
功能建议
详细说明，尽可能提供(伪)代码示例。
