Mapper中SQL语句的动态拼接（一）

筛选指定时间间隔内的数据

<if test="param.startTimeStr != null and param.startTimeStr != ''">
		and tb.CREATED_TIME &gt;= #{param.startTimeStr}
</if>
<if test="param.endTimeStr != null and param.endTimeStr != ''">
		and tb.CREATED_TIME &lt;= #{param.endTimeStr}
</if>

筛选指定列的模糊查询

<if test="param.name != null and param.name != ''">
		and tb.NAME like concat('%', #{param.name}, '%')
</if>

删除多ID，利用逗号拼接 in (…)

<delete id="deleteByProjectMenuIds">
        delete from 表名 where id in
	<foreach collection="projectMenuIds" item="projectMenuId" open="(" separator="," close=")">
            #{projectMenuId}
	</foreach>
</delete>

根据类型动态选择排序条件

SQL ...查询语句...
order by
<if test="param.sortType == null">
	tb.CREATED_TIME DESC
</if>
<if test="param.sortType != null">
	<if test="param.sortType == 1">
		tb.PROJECT_STATUS
	</if>
	<if test="param.sortType == 2">
		tb.PROJECT_STATUS desc
	</if>
	<if test="param.sortType == 3">
		tb.PROJECT_TOTAL_AMOUNT
	</if>
	<if test="param.sortType == 4">
		tb.PROJECT_TOTAL_AMOUNT desc
	</if>
</if>

根据检索条件查询

    select * from X.WD_ADM_DUTY_FAULT
        <where>
            <if test="dutyFault != null">
                <if test="dutyFault.title != null and dutyFault.title != ''">
                    and TITLE like concat('%', #{dutyFault.title}, '%')
                </if>
                <if test="dutyFault.createdBy != null and dutyFault.createdBy != ''">
                    and CREATED_BY = #{dutyFault.createdBy}
                </if>
                <if test="dutyFault.startTimeStr != null and dutyFault.startTimeStr != ''">
                    and CREATED_TIME &gt;= #{dutyFault.startTimeStr}
                </if>
                <if test="dutyFault.endTimeStr != null and dutyFault.endTimeStr != ''">
                    and CREATED_TIME &lt;= #{dutyFault.endTimeStr}
                </if>
            </if>
        </where>

Mapper 中传参：

	@Param("projectId") String projectId,@Param("unitIds") List<String> unitIds

SQL 语句：

  		and OA_SYS_DEPT_ID in
        <foreach collection="unitIds" item="unitId" index="index" open="(" close=")" separator=",">
            #{unitId}
        </foreach>

根据条件检索个人信息的SQL

  		<where>
            <if test="apply != null">
                <if test="apply.userId != null and apply.userId != ''">
                    and c.CREATED_BY = #{apply.userId}
                </if>
                <if test="apply.startTime != null and apply.startTime != ''">
                    and c.CREATED_TIME &gt;= #{apply.startTime}
                </if>
                <if test="apply.endTime != null and apply.endTime != ''">
                    and c.CREATED_TIME &lt;= #{apply.endTime}
                </if>
                <if test="apply.examState != null and apply.examState != ''">
                    and c.STATUS = #{apply.examState}
                </if>
            </if>
        </where>

查询一个字符的字符串时需要使用双引号，外层使用单引号

  		<if test="applyState != null">
            <if test='applyState == "1"'>
                and c.STATUS == 1
            </if>
            <if test='applyState == "2"'>
                and c.STATUS != 1
            </if>
        </if>

拼接固定内容至查询结果

select concat('%', '拼接内容1', '拼接内容2') as ADDITIONAL_COLUMNS

利用其他方法替换not in （SQL语句有长度上限）

select d.TARGET_ID
from X.WD_TARGET_FEEDBACK d
where d.ID not in
      (select MAIN_ID from X.WD_TARGET_EXAM c where c.MAIN_TYPE = 2 and c.IS_DELETED = 0)
group by d.TARGET_ID

替换为：

select d.TARGET_ID
from X.WD_TARGET_FEEDBACK d
         left join X.WD_TARGET_EXAM c
                   on d.ID = c.MAIN_ID and c.MAIN_TYPE = 2 and c.IS_DELETED = 0
where c.MAIN_ID is null
group by d.TARGET_ID

统计各个类别的数量

当状态=1时，统计count（1），如果统计其他的内容替换count，以及 then 和 end 之间的内容即可。

select	count(case when g.FEEDBACK_STATUS = 1 then 1 end) as NORMAL_COUNT,
		count(case when g.FEEDBACK_STATUS = 2 then 1 end) as FOLLOW_COUNT,
		count(case when g.FEEDBACK_STATUS = 3 then 1 end) as OVER_TIME_COUNT
	from X.WD_TARGET_TARGET 

利用分组用逗号拼接IDS或其他列

select group_concat(wasa.PUSH_ADDRESS_ID separator ',') as ids, wasa.SCHEDULESETTING_ID
from X.WD_ADM_SCHEDULE_ADDRESS wasa
        group by wasa.SCHEDULESETTING_ID;

但是达梦数据库使用 group_concat 一直报错，百度后发现将其替换为 wm_concat 。

select wm_concat(wasa.PUSH_ADDRESS_ID) as ids, wasa.SCHEDULESETTING_ID
from X.WD_ADM_SCHEDULE_ADDRESS wasa
		group by wasa.SCHEDULESETTING_ID;

默认为逗号分隔，如果想其他符号要重写函数或者用 replace 函数包裹替换，例如：

select replace(wm_concat(wasa.PUSH_ADDRESS_ID), ',', '|') as ids
from X.WD_ADM_SCHEDULE_ADDRESS wasa
		group by wasa.SCHEDULESETTING_ID;

如果还想支持排序，可以使用 listagg 和 within group 函数，例如：

select listagg(wasa.PUSH_ADDRESS_ID, '.') within group (order by wasa.SCHEDULESETTING_ID) as ids
from X.WD_ADM_SCHEDULE_ADDRESS wasa
group by wasa.SCHEDULESETTING_ID