公司同事写的sql

项目表（tb_project）、项目下用户表（tb_project_user）、项目交接表（tb_project_handover）

条件分页查询用户下项目交接列表

<select id="projectList" resultType="com.hnwel.modules.crc.dto.ProjectProjectHandoverDto"
            parameterType="com.hnwel.modules.crc.query.ProjectQuery">
        SELECT p.dept_id,
               p.dept_name,
               p.pi_name,
               p.id,
               p.project_name,
               p.trial_scheme_no,
               p.project_type,
               p.sponsor,
               p.smo_id,
               p.smo_name,
               p.start_time,
               h.handover_type,
               (select count(*) from tb_project_crc where project_id = p.id and status = 1) as numberPeople
        from tb_project_handover h
                 left join
             (
                 select *
                 from tb_project
                 where id in
                       (
                           select pu.project_id
                           from tb_project_user pu
                                    left join tb_project p on pu.id = p.id
                            <where>
                                pu.user_id = #{userId}
                                <if test="trialSchemeNo != null and trialSchemeNo != ''"> and pu.trial_scheme_no like concat(#{trialSchemeNo}, '%')</if>
                                <if test="deptName != null  and deptName != ''"> and pu.dept_name like concat(#{deptName}, '%')</if>
                                <if test="smoName != null  and smoName != ''"> and pu.smo_name like concat(#{smoName}, '%')</if>
                                <if test="startTime != null "> and pu.start_time = #{startTime}</if>
                            </where>
                       )
             ) p on h.project_id = p.id
        <where>
            <if test="handoverType != null and handoverType != ''"> and h.handover_type = #{handoverType}</if>
        </where>
        limit #{startRow},#{pageNumber}
    </select>