mybatis动态having

最新推荐文章于 2023-11-22 15:59:07 发布

N_P_E

最新推荐文章于 2023-11-22 15:59:07 发布

阅读量3.1k

点赞数 1

文章标签： mybatis mysql 数据库

本文链接：https://blog.csdn.net/m0_46689235/article/details/127788168

版权

需求：统计数据时要根据前端传的人员name，班次查询人员信息

动态拼接having条件【因为我这个字段不分组之前也可以过滤，所以说可以写在where里面，也可以分组之后再次过滤】
直接使用mybatis的<trim>标签就可以实现这个需求

SELECT
            substring_index( substring_index( u.zj_user_person_id, ',', b.help_topic_id + 1 ), ',', - 1 ) userId,
            substring_index( substring_index( u.zj_user_person_count, ',', b.help_topic_id + 1 ), ',', - 1 ) userCount,
            substring_index( substring_index( u.zj_user_person_time, ',', b.help_topic_id + 1 ), ',', - 1 ) statisticsTime,
            substring_index( substring_index( u.zj_user_person_classes, ',', b.help_topic_id + 1 ), ',', - 1 ) classes,
            substring_index( substring_index( u.zj_user_person_name, ',', b.help_topic_id + 1 ), ',', - 1 ) userName,
            substring_index( substring_index( u.zj_user_person_dept_name, ',', b.help_topic_id + 1 ), ',', - 1 ) deptName,
            SUM( CASE WHEN u.zj_user_worker_type = 45 THEN  IFNULL(substring_index( substring_index( u.zj_user_person_count, ',', b.help_topic_id + 1 ), ',', - 1 ),0)  ELSE 0 END ) zjLength,
            SUM( CASE WHEN u.zj_user_worker_type = 35 THEN  IFNULL(substring_index( substring_index( u.zj_user_person_count, ',', b.help_topic_id + 1 ), ',', - 1 ),0)  ELSE 0 END ) zjNumbers,
            SUM( CASE WHEN u.zj_user_worker_type = 40 THEN  IFNULL(substring_index( substring_index( u.zj_user_person_count, ',', b.help_topic_id + 1 ), ',', - 1 ),0)  ELSE 0 END ) changeVariety,
            SUM( CASE WHEN u.zj_user_worker_type = 34 THEN  IFNULL(substring_index( substring_index( u.zj_user_person_count, ',', b.help_topic_id + 1 ), ',', - 1 ),0)  ELSE 0 END ) twistedSilk,
            SUM( CASE WHEN u.zj_user_worker_type = 29 THEN  IFNULL(substring_index( substring_index( u.zj_user_person_count, ',', b.help_topic_id + 1 ), ',', - 1 ),0)  ELSE 0 END ) head,
            SUM( CASE WHEN u.zj_user_worker_type = 33 THEN  IFNULL(substring_index( substring_index( u.zj_user_person_count, ',', b.help_topic_id + 1 ), ',', - 1 ),0)  ELSE 0 END ) overKnot,
            SUM( CASE WHEN u.zj_user_worker_type = 42 THEN  IFNULL(substring_index( substring_index( u.zj_user_person_count, ',', b.help_topic_id + 1 ), ',', - 1 ),0)  ELSE 0 END ) changeSy,
            SUM( CASE WHEN u.zj_user_worker_type = 43 THEN  IFNULL(substring_index( substring_index( u.zj_user_person_count, ',', b.help_topic_id + 1 ), ',', - 1 ),0)  ELSE 0 END ) upShaft,
            SUM( CASE WHEN u.zj_user_worker_type = 47 THEN  IFNULL(substring_index( substring_index( u.zj_user_person_count, ',', b.help_topic_id + 1 ), ',', - 1 ),0)  ELSE 0 END ) rawSilkPerm,
            SUM( CASE WHEN u.zj_user_worker_type = 38 THEN  IFNULL(substring_index( substring_index( u.zj_user_person_count, ',', b.help_topic_id + 1 ), ',', - 1 ),0)  ELSE 0 END ) pickS,
            SUM( CASE WHEN u.zj_user_worker_type = 27 THEN  IFNULL(substring_index( substring_index( u.zj_user_person_count, ',', b.help_topic_id + 1 ), ',', - 1 ),0)  ELSE 0 END ) dismantleShaftLength,
            SUM( CASE WHEN u.zj_user_worker_type = 32 THEN  IFNULL(substring_index( substring_index( u.zj_user_person_count, ',', b.help_topic_id + 1 ), ',', - 1 ),0)  ELSE 0 END ) reduceHead,
            SUM( CASE WHEN u.zj_user_worker_type = 31 THEN  IFNULL(substring_index( substring_index( u.zj_user_person_count, ',', b.help_topic_id + 1 ), ',', - 1 ),0)  ELSE 0 END ) plusHead,
            u.zj_user_material_batch_no materialBatchNo,
            u.zj_user_material_name materialName,
            CONCAT( u.zj_user_material_name,' 	', u.zj_user_material_batch_no ) AS materialNameAndBatchNo
    FROM
            busi_zj_user u
    LEFT JOIN help_topic b ON b.help_topic_id &lt; ( LENGTH( u.zj_user_person_id ) - LENGTH( REPLACE ( u.zj_user_person_id, ',', '' ) ) + 1 )
    WHERE
            u.is_delete = 0
            AND u.zj_user_org_id = #{cropOrgId}
            AND u.zj_user_worker_type NOT IN ( 25, 26, 27 )
            <if test="beginTime != null and beginTime != ''"><!-- 开始时间检索 -->
                    AND date_format(u.zj_user_start_time,'%Y-%m-%d') &gt;= date_format(#{beginTime},'%Y-%m-%d')
            </if>
            <if test="endTime != null and endTime != ''"><!-- 结束时间检索 -->
                    AND date_format(u.zj_user_start_time,'%Y-%m-%d') &lt;= date_format(#{endTime},'%Y-%m-%d')
            </if>
    GROUP BY
            deptName,
            userId,
            date_format( statisticsTime, '%Y-%m-%d' ),
            materialBatchNo,
            materialName
        <trim prefix="HAVING"  prefixOverrides="AND">
            <if test="userName !=null and userName !='' ">
                AND userName LIKE CONCAT('%',#{userName},'%')
             </if>
            <if test="classes !=null and classes !='' ">
                AND classes LIKE CONCAT('%',#{classes},'%')
             </if>
        </trim>
    ORDER BY
            statisticsTime DESC,
            userId DESC