sql模糊查询和根据日期筛选

<!-- 查询统计列表 -->   &gt;= 小于等于      &lt;=大于等于

 

 

将字符类型转换成日期格式进行比较

select * from xy_platformMessage
<where>
<if test="approval_status !=null and approval_status !=''">
and approval_status=#{approval_status}
</if>
<if test="start_date !=null and start_date !=''">
and STR_TO_DATE(announcement_date,'%Y-%m-%d') &gt;= STR_TO_DATE(#{start_date},'%Y-%m-%d')
</if>
<if test="end_date !=null and end_date !=''">
and STR_TO_DATE(announcement_date,'%Y-%m-%d') &lt;= STR_TO_DATE(#{end_date},'%Y-%m-%d')
</if>
</where>
order by announcement_date desc

<select id="findStatisticalRecord" parameterType="java.util.Map" resultType="java.util.Map">
select u.*,p.ProductName from xy_pdf_untowardReaction u left join xy_ccproduct p
on u.SerialNum=p.SerialNum
where u.company_id=#{company_id}
<if test="ProductName != null">
and (p.ProductName like '%%${ProductName}%%')

</if>
<if test="start_date != null and start_date!=''">
and STR_TO_DATE(u.add_time,'%Y-%m-%d') &gt;= STR_TO_DATE(#{start_date},'%Y-%m-%d')

</if>
<if test="end_date != null and end_date!=''">
and #{end_date} &gt;= STR_TO_DATE(u.add_time,'%Y-%m-%d')
</if>
order by u.add_time desc
</select>

 

 

2.

<select id="GetList" resultType="java.util.Map" parameterType="com.xy.cc.bean.ComputeParam">
SELECT
`id`,
`no`,
`SerialNum`,
`date_of_occurrence`,
`time_frame`,
`interval_month`,
`birthday`,
`sex`,
`weight`,
`nation`,
`height`,
`city_a`,
`human_race`,
`untoward_reaction_name`,
`level`,
`new_or_old`,
`table`
FROM
xy_survey_compute
WHERE SerialNum=#{SerialNum}
and company_id=#{company_id}
AND STR_TO_DATE(`date_of_occurrence`,'%Y-%m-%d %H:%i:%s') &gt;= STR_TO_DATE(CONCAT(#{start_date},' 00:00:01'),'%Y-%m-%d %H:%i:%s')
AND STR_TO_DATE(`date_of_occurrence`,'%Y-%m-%d %H:%i:%s') &lt;=STR_TO_DATE(CONCAT(#{end_date},' 23:59:59'),'%Y-%m-%d %H:%i:%s')

</select>