sqlServer使用pageHelper需要order by时会出现bug

sqlServer使用pageHelper需要order by时会出现bug

1、比如你的sql语句是这样的。

// An highlighted block
<if test=" pyCode!=null and pyCode!='' ">
    <bind name="pyCode1" value="'%'+pyCode+'%'"/>
    <bind name="pyCode2" value="pyCode+'%'"></bind>
    <bind name="pyCode3" value="'%'+pyCode"/>
</if>
        select id,mc,pybm from
            his_zd_jcxm
        <where>
            <if test=" pyCode!=null and pyCode!='' ">
                pybm like #{pyCode1}
            </if>
        </where>
        order by
        case
            when pybm like #{pyCode2} then 0
            when pybm like #{pyCode1} then 1
            when pybm like #{pyCode3} then 2
        end,pybm,mc

2、这一步经过mybatis的参数解析是不会出问题的。但问题会出在pageHelper会重写分页语句，如下。

// An highlighted block
SELECT TOP 10 id, mc, pybm FROM (SELECT ROW_NUMBER() OVER (ORDER BY ROW_ALIAS_1, pybm, mc, id) PAGE_ROW_NUMBER, id, mc, pybm FROM (SELECT id, mc, pybm, CASE WHEN CHARINDEX(?, pybm) = 1 THEN 0 ELSE 1 END AS ROW_ALIAS_1 FROM his_zd_jcxm WHERE pybm LIKE '%' + ? + '%') AS PAGE_TABLE_ALIAS) AS PAGE_TABLE_ALIAS WHERE PAGE_ROW_NUMBER > 0 ORDER BY PAGE_ROW_NUMBER

3、注意看参数顺序，where条件的参数已经从我们mybatis语句中的第一位，变到最后一位了，这样参数就错位了，得出来的结果自然会出现问题。（文章如有不对，希望大佬们指点）