一个搜索框查多个字段(mysql concat_ws() 函数)
下面展示一些 内联代码片
。
<!-- 全字段检索分页查询 -->
<select id="searchPage" resultMap="MineBadHoleVO">
SELECT
A.*,B.location_x,B.location_y,B.location_z,B.geographical_location,B.engineering_id
FROM
mine_bad_hole_book A
LEFT JOIN mine_location_book B ON A.location_id = B.id
WHERE A.del_flag = 0
<!-- DATE_FORMAT() 函数 可以显示时间的不同格式 -->
<if test="query.yearSearch != null and query.yearSearch != ''">
AND date_format(A.hole_time,'%Y') = #{query.yearSearch}
</if>
<!-- concat_ws() 将多个字符串连接成一个字符串
这样就可以通过一个查询框查询多个字段的内容 -->
<if test="query.search != null and query.search != ''">
AND CONCAT_WS(',',A.hole_num,A.hole_time,A.complete_time,
A.hole_position,A.hole_depth,A.hole_horizon,A.hole_distance,
A.cement_sand_weight,A.quality_level)
LIKE CONCAT('%',#{query.search},'%')
</if>
<if test="query.coalId != null and query.coalId != ''">
AND A.coal_id = #{query.coalId}
</if>
<if test="query.orderBy == null or query.orderBy ==''">
ORDER BY A.hole_time DESC
</if>
<if test="query.orderBy != null and query.orderBy != '' ">
ORDER BY ${query.orderBy}
</if>
</select>