需求图片,查询几天内过期的数据
DATE_ADD(CURDATE(), INTERVAL 时间间隔 DAY) >= 到期时间
相反的函数 DATE_SUB()
不确定修改字段(必须修改一个,否则会报错)
<update id="changeStatusTerminEquipByIds" parameterType="pd">
update
<include refid="tableName"></include>
<trim prefix="SET" suffixOverrides=",">
<if test="STATUS !=null and STATUS !=''">
STATUS =#{STATUS },
</if>
<if test="RID !=null and RID !=''">
RID =#{RID },
</if>
</trim>
where TERMINALEQUIPMENTS_ID in (${ids})
</update>
使用IFNULL()处理空数据
<select id="getSimpleEquByOrgId" parameterType="page" resultType="pd">
SELECT
IFNULL(tequ.`ORGSTRUCTURES_ID`,0) A1,
IFNULL(tequ.`ORGSTRUCTURENAME`,"默认组") A2,
IFNULL(tequ.`EQUIPMENTNAME`,tequ.`TERMINALEQUIPMENTNUM`) A3,
tequ.`PMFINTERPRETERID` A4,
tequ.`TERMINALEQUIPMENTSTATUS` A6,
CASE tequ.`TERMINALEQUIPMENTSTATUS`
WHEN 0 THEN '使用中'
WHEN 1 THEN '未启用'
WHEN 2 THEN '已到期'
WHEN 10 THEN '测试'
END AS A7,
tequ.`TERMINALEQUIPMENTNUM` IMEI
FROM `bd_terminalequipments` tequ``
</select>
使用GRUOP_CONCAT()以及CONCAT函数,将查询出来TERMINALEQUIPMENTS_ID字段的所有值拼成:“1”,“2”,“3”带引号的数据
<!--查询已过期设备 状态为使用中的 条数或者服务时间到期-->
<select id="queryOverdueTerminEquip" resultType="String">
SELECT GROUP_CONCAT(concat('"',TERMINALEQUIPMENTS_ID,'"') separator ',')
from bd_terminalequipments
where TERMINALEQUIPMENTSTATUS=0
and (NOW()>=SERVICELIFEBYTIME
or HAVELOCATENUMBER>=SERVICELIFEBYCOUNT)
</select>
使用date_format()转换格式,查询本月的数据
<!--根据组织id,查询激活时间在本月内且状态在使用中0,的终端设备数量-->
<select id="queryTerminEquipWeekCount" parameterType="pd" resultType="Integer">
select count(1)
from
bd_terminalequipments
where
date_format(TERMINALEQUIPMENTACTIVATETIME,'%Y-%m')=date_format(now(),'%Y-%m')
and TERMINALEQUIPMENTSTATUS=0
<if test="orgId!=null and orgId!= ''">
and ORGENTERPRISEID=#{orgId}
</if>
</select>
批量操作格式:
<!--批量修改设备-->
<update id="distributeEquip" parameterType="pd">
update
<include refid="tableName"></include>
set ORGENTERPRISEID=#{ORGENTERPRISEID}
where TERMINALEQUIPMENTS_ID in
<foreach item="item" open="(" close=")" separator="," index="index" collection="ids">
#{item}
</foreach>
</update>
<!-- 批量删除 -->
<delete id="deleteAll" parameterType="String">
delete from
<include refid="tableName"></include>
where TERMINALEQUIPMENTS_ID in
<foreach item="item" index="index" collection="array" open="(" separator="," close=")">
#{item}
</foreach>
</delete>
<insert id="addTrainRecordBatch" useGeneratedKeys="true" parameterType="java.util.List">
<selectKey resultType="long" keyProperty="id" order="AFTER">
SELECT
LAST_INSERT_ID()
</selectKey>
insert into t_train_record (add_time,emp_id,activity_id,flag)
values
<foreach collection="list" item="item" index="index" separator="," >
(#{item.addTime},#{item.empId},#{item.activityId},#{item.flag})
</foreach>
</insert>