原表 user:
id name roleId
1 aaa 1
2 bbb 2
3 ccc 3
4 ddd 4
5 eee 51,MySQL可以通过field()函数自定义排序,格式:field(value,str1,str2,str3,str4),value与str1、str2、str3、str4比较,返回1、2、3、4,如遇到null或者不在列表中的数据则返回0.
例如:select * from user order by field(roleId,2,3,4,1,5);
结果:
id name roleId
2 bbb 2
3 ccc 3
4 ddd 4
1 aaa 1
5 eee 5
2,locate(substr,str)函数返回子串substr在字符串str中第一次出现的位置,可以根据该函数进行排序
例如:select * from user order by locate(id,'2,3,1,5,4');
结果:
id name roleId
2 bbb 2
3 ccc 3
1 aaa 1
5 eee 5
4 ddd 4
另外附一个用运在myBatis中的真实项目代码
PRO_PAY_ID是险种id
<!--查询需要财务申请的所有信息,五险不合一--> <select id="findApplyFnncPayAll" resultType="com.sinosoft.cpyy.controller.apply.vo.ApplyFnncPayVO" parameterType="com.sinosoft.cpyy.controller.apply.dto.ApplyFnncPayDTO"> SELECT ST.PAY_FEE_AMO,ST.SINS_ACCT_ID,ST.EXECUTE_MON,ST.EXECUTE_MON AS applyMon,SP.PAY_VOUCH_NO, SP.EAV_STATE,SP.PAY_STATE,SP.CRE_ID,SP.CRE_TIME AS applyTime,SA.UNIT_SINS_NO,SA.SINS_ACCT_NAME, SA.AREA_ID,PRO_PAY_ID,SP.PAY_AMO FROM SINS_ACCT_MONTHLY_TOTAL ST JOIN SINS_ACCT_INFO SA ON SA.SINS_ACCT_ID=ST.SINS_ACCT_ID LEFT JOIN SINS_APPLY_FNNC_PAY SP ON SP.SINS_PAY_INFO_ID=ST.ORDER_NO WHERE 1=1 <if test="isEffect !=null ">AND ST.IS_EFFECT=#{isEffect}</if> <if test="isEffect !=null ">AND SA.IS_EFFECT=#{isEffect}</if> <if test="isEffect !=null ">AND (SP.IS_EFFECT IS null OR SP.IS_EFFECT=#{isEffect})</if> <if test="unitSinsNo !=null and unitSinsNo !=''">AND SA.UNIT_SINS_NO =#{unitSinsNo}</if> <if test="sinsAcctId !=null">AND SA.SINS_ACCT_ID =#{sinsAcctId}</if> <if test="areaId !=null">AND SA.AERA_ID =#{areaId}</if> <if test="eavState !=null and eavState!=1">AND SP.EAV_STATE=#{eavState}</if> <if test="eavState =1">AND (SP.EAV_STATE IS null OR SP.EAV_STATE=#{eavState})</if> <if test="payState !=null and payState !=1">AND SP.PAY_STATE=#{payState}</if> <if test="payState == 0">AND (SP.PAY_STATE IS NULL OR SP.PAY_STATE=#{payState})</if> <if test="applyMon !=null">AND ST.APPLY_MON=#{applyMon}</if> ORDER BY SA.CRE_TIME DESC,LOCATE(ST.PRO_PAY_ID,'4,5,3,2,1,6') </select>