sql case when then 语法使用

select distinct
        isa.site_id as siteId,
        sb.site_name as siteName,
        isf.id as id,
        isad.type_id as typeId,
        ist.type_name as typeName,
        isf.create_user_id as commitUserId,
        isad.content_item_id as contentItemId,
        u.user_phone as commitTelephone,
        case
        when isad.content_item_id IS NOT NULL THEN isci.item_name
        when isad.content_item_id IS NULL THEN isad.other_item_name
        end as contentItemName
        from table

1.这是在查询返回字段赋不同的值时候的使用

 

<where>
            <if test="payfor != null and payfor != ''">

                (case when d.type = 3
                then (a.supplier_name LIKE #{payfor}"%")
                when d.type = 2 or d.type = 5
                then d.customer_name LIKE #{payfor}"%" end)
            </if>

</where>

2.在where子句中的使用（mybatis中）

 

 

select c.*,
     (CASE t.is_like
     WHEN '1' THEN '1'
     ELSE '0'
     END) as isLike,
    (CASE o.`status`
    WHEN '1' THEN '1'
    ELSE '0'
    END) as isPay
        from  c
                  left JOIN t on c.course_id=t.course_id and t.user_id=#{userId}
                  left join o on c.course_id=o.course_id and o.user_id = #{userId

3. 常规用法 ，left JOIN 之后 可能出现空值的情况  ，o是订单表 根据订单状态判断是否已支付