mysql 某列指定值靠前排序：order by 高级用法之case when

做项目的时候遇到特殊的排序，排序主要写sql语句进行排序

需求是：查询出的专员列表，根据用户找到相同街道然后找到相同社区>转介人数排序

数据库执行

方式一

SELECT * FROM td_street_commissioner order by (
    case when street_code='500103012' then 1 when org_code='001008003' then 2 ELSE 3 END),people_number desc;

case when 当street_code等于500103012的时候返回1 ，org_code等于"001008003"的时候返回2，都不满足前面条件的时候返回3，然后再按照case when返回的值排序

等同于：

方式二

SELECT street_code,org_code community_code,people_number,
case when street_code='500103012' then 1 when org_code='001008003' then 2 ELSE 3 END as sortCode
FROM td_street_commissioner
where hospital_id=6612275265143874816 and status=1 and logical_deletion=0
order by sortCode,people_number desc;

mybatis中执行

注意：在mybatis中用动态sql，order by 后面的条件需要用$，我开始用#传值报错

方式一

order by后面用#取值报错

    <select id="orderPage" resultMap="BaseResultMap">
        SELECT street_code,org_code,people_number FROM td_street_commissioner
        where hospital_id=#{req.hospitalId} and status=1 and logical_deletion=0
        order by (
            case when street_code=#{req.streetCode} then 1 when org_code=#{req.orgCode} then 2 ELSE 3 END),people_number desc
    </select>

执行结果：

 用$能正常取值

    <select id="orderPage" resultMap="BaseResultMap">
        SELECT street_code,org_code,people_number FROM td_street_commissioner
        where hospital_id=#{req.hospitalId} and status=1 and logical_deletion=0
        order by (
            case when street_code=${req.streetCode} then 1 when org_code=${req.orgCode} then 2 ELSE 3 END),people_number desc
    </select>

执行结果： 可以看到$取值是预编译的时候直接解析成sql语句，而#则是?占位符

上面用的是mybatis plus，后面用mybatis项目测试了一下，发现在mybatis中order by中可以用#取值；那问题就出在苞米豆上面，因为苞米豆分页拦截时对sql进行了处理

    <select id="selA" resultMap="AppDrugUseMasterResult">
        select date_format(dum.use_date,'%Y-%m') useDateMonth, dum.id,dum.drug_name,dum.frequency
        from drug_use_master dum
        order by (case when dum.frequency=#{state} then 1 ELSE 3 END)
    </select>

 方式二

用#能正常取值

    <select id="orderPage" resultMap="BaseResultMap">
        SELECT street_code,org_code community_code,people_number,
        case when street_code=#{req.streetCode} then 1 when org_code=#{req.orgCode} then 2 ELSE 3 END as sortCode
        FROM td_street_commissioner
        where hospital_id=#{req.hospitalId} and status=1 and logical_deletion=0
        order by sortCode,people_number desc
    </select>

执行结果：