业务需求:根据时间区间来筛选列表
数据库对应的字段如下:
sql语句如下:
SELECT
a.pre_machine_name,
b.*,
c.type_name,
d.status_type
FROM
data_center_pre_machine a,
data_center_pre_library b,
dic_type c,
dic_status d
WHERE
a.del_flag = 0
AND b.del_flag = 0
AND a.id = b.pre_machine_id
AND b.pre_library_type = c.id
AND date_format( b.create_date, '%Y-%m-%d %T' ) >= date_format('2021-05-01 17:05:29','%Y-%m-%d %T')
AND date_format('2021-05-12 16:08:32','%Y-%m-%d %T') >= date_format(b.create_date,'%Y-%m-%d %T')
在mybatis中的xml配置如下:
<select id="getPreLibraryList" resultMap="PreMachineVoMap">
select
a.pre_machine_name,b.*,c.type_name,d.status_type
from
data_center_pre_machine a,data_center_pre_library b,dic_type c,dic_status d
where
a.del_flag=0 and b.del_flag=0 and a.id=b.pre_machine_id and b.pre_library_type=c.id
<if test="libraryName!=null and libraryName!=''">
and library_name like concat ('%',#{libraryName},'%')
</if>
<if test="preLibraryType!=null and preLibraryType!=''">
and b.pre_library_type=#{preLibraryType}
</if>
<if test="status!=null and status!=''">
and b.status=#{status}
</if>
<if test="startDate!=null and startDate!=''">
and date_format(b.create_date,'%Y-%m-%d %T') >= date_format(#{startDate},'%Y-%m-%d %T')
</if>
<if test="endDate!=null and endDate!=''">
and date_format(#{endDate},'%Y-%m-%d %T') >= date_format(b.create_date,'%Y-%m-%d %T')
</if>
</select>