sql case when then 语法使用

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14

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是订单表 根据订单状态判断是否已支付