给大家举个栗子:
<select id="selectPositionRecordByPageCount" resultType="java.lang.Long">
SELECT
COUNT(1)
FROM
t_oa_adjust_position_record r
INNER JOIN t_oa_employee e ON e.oid=r.employee_oid
INNER JOIN t_oa_employee_details e1 ON r.employee_oid = e1.employee_oid
INNER JOIN t_oms_user u ON e.user_oid = u.oid
INNER JOIN t_oms_org o ON u.dept_id = o.oid
<where>
<include refid="query_positionRecord_where"></include>
</where>
</select>
<select id="selectPositionRecordByPage" resultType="SelectPositionRecordByPage">
SELECT
r.oid,
e.name,
o.name department,
e.position,
e.work_number,
e1.entry_time,
approval_initiate_date,
before_adjust_department,
after_adjust_department,
before_adjust_position,
after_adjust_position,
before_adjust_rank,
after_adjust_rank,
position_adjust_start_time,
position_adjust_type,
position_adjust_reason
FROM
t_oa_adjust_position_record r
INNER JOIN t_oa_employee e ON e.oid=r.employee_oid
INNER JOIN t_oa_employee_details e1 ON r.employee_oid = e1.employee_oid
INNER JOIN t_oms_user u ON e.user_oid = u.oid
INNER JOIN t_oms_org o ON u.dept_id = o.oid
<where>
<include refid="query_positionRecord_where"></include>
</where>
order by e.oid desc,r.position_adjust_start_time desc,r.updated_at desc
limit #{pageIndex},#{pageSize};
</select>
<sql id="query_positionRecord_where">
<if test="name != null and name != ''">
and e.name like #{name}
</if>
<if test="department != null and department != ''">
and u.dept_id in
<foreach collection="department" item="departmentId" index="index"
open="(" close=")" separator=",">
#{departmentId}
</foreach>
</if>
<if test="position != null and position != ''">
and e.position=#{position}
</if>
<if test="workNumber != null and workNumber != ''">
and e.work_number like #{workNumber}
</if>
<if test="approvalInitiateDate != null and approvalInitiateDate != ''">
and r.approval_initiate_date=#{approvalInitiateDate}
</if>
<if test="positionAdjustType != null and positionAdjustType != ''">
and r.position_adjust_type=#{positionAdjustType}
</if>
</sql>
本人做项目用的是框架是SSM,在mybatis下sql语句是写在xml里的,大家可以看到where条件里面引入了一段sql,在这段sql中用的基本是“=”,有需要模糊查询的就用"like",其中有一个字段因为传过来的是一个list集合,故做了上述处理。
另外说一下,还加了分页噢!
希望能帮到你!学习路上共勉!