<select id="selectList" resultMap="BaseResultMap"> SELECT tg.id,tg.code,GROUP_CONCAT(DISTINCT tpi.name) placeNames ,GROUP_CONCAT(DISTINCT tgs.name) gridStaffNames,GROUP_CONCAT(DISTINCT tgs.tel) gridStaffTels, tg.grid_name,tg.short_name,tg.group_id,tg.number,tg.coordinates,tg.color,tg.center_coordinates,tg.status,tg.grid_man,tg.tel,tg.remark,tg.create_time, tg.create_account,tg.create_account_name,tgg.group_name groupName,tg.modify_time,tg.modify_account FROM t_grid tg LEFT JOIN t_place_grid tpg ON tg.id=tpg.grid_id LEFT JOIN t_place_info tpi ON tpi.id=tpg.place_id LEFT JOIN t_grid_group tgg ON tg.group_id=tgg.id LEFT JOIN t_grid_staff tgs ON tg.id=tgs.grid_id where tg.status != -1 <if test="placeIdList != null"> and tpg.place_id in <foreach collection="placeIdList" item="item" close=")" open="(" separator=","> #{item} </foreach> </if> <if test="searchContent != null and searchContent != ''"> and(tgg.group_name like concat('%',#{searchContent},'%') or tgg.short_name like concat('%',#{searchContent},'%') or tg.grid_name like concat('%',#{searchContent},'%') or tg.short_name like concat('%',#{searchContent},'%') or tgg.group_man like concat('%',#{searchContent},'%') or tgs.name like concat('%',#{searchContent},'%')) </if> <if test="status != null"> and tg.status = #{status} </if> <if test="arrIds != null"> and tg.id in <foreach collection="arrIds" index="index" open="(" item="item" close=")" separator=","> #{item} </foreach> </if> group by tg.id order by tg.create_time desc,tg.grid_name desc </select>
<select id="getFaWaterLevelList" resultType="java.util.Map"> SELECT water_level `value`,DATE_FORMAT(upload_time, '%Y-%m-%d %H:%i:%s')as `date` FROM t_fa_water_level_pressure_sensor_data where device_id = #{deviceId} and `upload_time` > DATE_ADD(date(now()),INTERVAL -29 day) and del_flag = 0 order by upload_time asc </select>
DATE_FORMAT 用法:参考链接 https://www.w3school.com.cn/sql/func_date_format.asp
DATE_ADD 用法:参考链接https://blog.csdn.net/weixin_39921821/article/details/108095060