常用函数之case-when-THEN-else-end

1、引入sql：<include refid="getPageSql"></include> 

2、

CASE  enable_status
WHEN '00' THEN '未启用'
WHEN '01' THEN '已启用'
ELSE enable_status
END  as enableStatusName,

enable_status-列名

 

 

<!--分页查询字段（模板+审核）-->
    <!-- case when是判断值转换代码时使用-->
    <sql id="getPageSql">
                CASE  enable_status
                WHEN '00' THEN '未启用'
                WHEN '01' THEN '已启用'
                ELSE enable_status
                END  as enableStatusName,
            CASE  audit_status
                WHEN '00' THEN '待提交'
                WHEN '01' THEN '待审核'
                WHEN '02' THEN '通过'
                WHEN '03' THEN '不通过'
                ELSE audit_status
            END  as auditStatusName,
            a.*
                ,b.category_name
                ,c.category_small_name
                ,d.specs_model
</sql>
    <!--分页查询list-->
    <select id="getPage" resultMap="BaseResultMap">
    SELECT
    <include refid="getPageSql"></include>
    FROM
    mda_data_acquisition a
    LEFT JOIN mda_category b on a.category_code =b.category_code
    LEFT JOIN mda_category_small c on a.category_small_code =c.category_small_code
    LEFT JOIN mda_specs_model d on a.specs_model_code =d.specs_model_code
    <where>
        <if test="categoryCode != null and categoryCode != ''">
            and a.category_code = #{categoryCode,jdbcType= VARCHAR}
        </if>
        <if test="categorySmallCode != null and  categorySmallCode != ''">
            and a.category_small_code = #{categorySmallCode,jdbcType= VARCHAR}
        </if>
        <if test="specsModelCode != null and specsModelCode != ''">
            and a.specs_model_code LIKE CONCAT('%',#{specsModelCode,jdbcType=VARCHAR},'%')
        </if>
        <if test="modelCode != null and modelCode != ''">
            and a.model_code LIKE CONCAT('%',#{modelCode,jdbcType=VARCHAR},'%')
        </if>
        <if test="specsModel != null and specsModel != ''">
            and d.specs_model LIKE CONCAT('%',#{specsModel,jdbcType=VARCHAR},'%')
        </if>
        <if test="auditStatus != null and auditStatus != ''">
            and a.audit_status = #{auditStatus,jdbcType=VARCHAR}
        </if>
        <if test="enableStatus != null and enableStatus != ''">
            and a.enable_status = #{enableStatus,jdbcType=VARCHAR}
        </if>
    </where>