mybatis中case when用法以及@Param用法

https://libusi.blog.csdn.net/article/details/108044060select d.tools_name, null as sum,null as num,dtp.specs,dtp.type,count()count( case when state =0 then 'state' end ) as 在库 ,count(case when state =

hero.fei

7295人浏览 · 2021-04-15 22:30:54

hero.fei · 2021-04-15 22:30:54 发布

https://libusi.blog.csdn.net/article/details/108044060

select d.tools_name, null as sum,null as num,dtp.specs,dtp.type,
count()
count( case when state =0 then 'state' end ) as 在库 ,
count(case when state =1 then 'state' end) 借出 ,
count(case when state =2 then 'state' end) 丢失,
count(case when state =3 then 'state' end) 报废
from dm_tools_KC d left join dm_tools_project dtp on d.tools_name = dtp.tools_name where d.TOOLS_NAME ='BH' group by d.tools_name,dtp.specs,dtp.type;

上面这个sql就是统计出不同工具名称的各个状态的数量，按工具名称进行分组统计

mapper中直接使用@Param注解的情况

int batchDelDataByIDs(@Param("idList") List<Long> idList, @Param("updateUserCode") String updateUserCode, @Param("updateUser") String updateUser);
<update id="batchDelDataByIDs">
        update `recybag_storage`
        set is_delete = 1,update_user=#{updateUserCode, jdbcType=VARCHAR},
        update_user_name=#{updateUser,jdbcType=VARCHAR},
        update_time=now()
        where
        is_delete = 0
        and id in
        <foreach collection="idList" item="id" separator="," open="(" close=")">
            #{id, jdbcType=BIGINT}
        </foreach>
    </update>


SELECT s.finance_center_code,s.area_code,s.site_code,s.site_name,
        COUNT(1) countNum,
               COUNT(case store_status when 0 then 1 end ) unUseNum ,
        COUNT(case store_status when 1 then 1 end ) unUseNum ,
        COUNT(case store_status when 2 then 1 end ) useNum ,
        COUNT(case store_status when 3 then 1 end ) completeNum ,
        COUNT(case store_status when 4 then 1 end ) exceptNum ,
        COUNT(case store_status when 5 then 1 end ) lostNum from recybag_storage s
        where is_delete= 0 and s.store_status in (1,2,3,4,5) GROUP BY s.site_code

case when简单用法：
CASE expression
        WHEN condition1 THEN result1
        WHEN condition2 THEN result2
        ...
        ELSE result
    END
   搜索函数：
CASE
        WHEN condition1 THEN result1
        WHEN condition2 THEN result2
        ...
    ELSE result
    END
   两种不同写法：
1)set c_age=case id when #{cus.id} then {cus.age} end

2)set c_name=case when id=#{cus.id} then #{cus.name} end
当有多个条件作为分组条件时可用如下case when语法：
GROUP BY (case when 1=#{queryType,jdbcType=INTEGER} then province_org_code
                       when 2=#{queryType,jdbcType=INTEGER} then slice_org_code
                       when 3=#{queryType,jdbcType=INTEGER} then partition_org_code else site_code end
        )
CASE 表示函数开始，END 表示函数结束。如果 condition1 成立，则返回 result1, 如果 condition2 成立，则返回result2，当全部不成立则返回else后面的 result，而当有一个成立之后，后面的就不执行了。

choose when也可以起到相同的作用如下：

<choose>
    <when test="orderField != null and orderField == 3"><!-- 揽收时间，降序 -->
        order by collect_time desc
    </when>
    <when test="orderField != null and orderField == 4"><!-- 揽收时间，升序 -->
        order by collect_time asc
    </when>
    <otherwise><!-- 默认排序：揽收时间，降序 -->
        order by collect_time desc
    </otherwise>
</choose>