sql查询实战代码

<select id="workPage" resultType="com.shsajt.db.model.ContractWork">
		select * from contract_work cw
		left join contract_item ci on cw.item_id = ci.id
		left join closed_loop cl on cw.id = cl.work_id
		<where>
			cw.deleted = 0 and ci.deleted = 0			
			<if test="vo.workType != null">
                and cw.work_type_id = #{vo.workType}
            </if>
            <if test="vo.itemId != null">
                and cw.item_id = #{vo.itemId}
            </if>
            <if test="vo.subTypeId != null">
                and cw.sub_type_id = #{vo.subTypeId}
            </if>
			<if test="vo.name != null">
				and cw.name like concat('%', #{vo.name}, '%')
			</if>
			<if test="vo.contractId != null">
				and ci.contract_id = #{vo.contractId}
			</if>
			<if test="vo.beginDate != null">
				and cw.begin_date <![CDATA[ >= ]]> #{vo.beginDate}
			</if>
			<if test="vo.endDate != null">
				and cw.end_date <![CDATA[ <= ]]> #{vo.endDate}
			</if>
			<if test="vo.officeId != null"> 
				and cw.office_id = #{vo.officeId}
			</if>
			
			<if test="vo.beginCreate != null">
                and cw.gmt_create <![CDATA[ >= ]]> #{vo.beginCreate}
            </if>
            <if test="vo.endCreate != null">
                and cw.gmt_create <![CDATA[ <= ]]> #{vo.endCreate}
            </if>
            <if test="vo.serveObjectId != null">
                and cw.serve_object = #{vo.serveObjectId}
            </if>
            
            <if test ="vo.looped != null and vo.looped == 1">
                 <!--  如果looped传1，代表查询的是抽查页面查询，就只能查出闭环的检查-->
                 and   cl.looped = 1     
            </if>
            
            <if test ="vo.looped == null and  vo.state != null and vo.state.size() >0">
            <!--  如果looped不传，代表查询的是企业服务合同下面各种合同执行，就是正常查-->
                and  cw.work_state_id  in
               <foreach collection='vo.state' item='item' index='index' open='(' separator=',' close=')'>  
                    #{item}
               </foreach>   
            </if>
            
			<if test ="vo.looped != null and vo.looped == 0 and vo.state != null and vo.state.size() >0">
			  <!--  如果looped传0，代表查询的是小程序的待完成模块的未闭环查询分页：要查出检查的所有状态和非检查的1.2.4.5四种状态 
			   还要配合下面的两个if条件一齐查出 3审核状态下面的非闭环情况-->
				and 
				(
				    ( cw.work_type_id = 8 and work_state_id in (1,2,3,4,5) )
                    
                    or 
                    
                    (cw.work_type_id != 8 and cw.work_state_id  in
		               <foreach collection='vo.state' item='item' index='index' open='(' separator=',' close=')'>  
		                    #{item}
		               </foreach>  
                    )    
               )     
            </if>
                        
            <if test="vo.looped != null and vo.looped == 0 and vo.creatorId != null and vo.permission == false">
                 and cw.creator_id = #{vo.creatorId}
                 and  (cl.looped = 0 or cl.looped is null)   
                
            </if>
                       
            <if test="vo.looped != null and vo.looped == 0 and vo.creatorId != null and vo.permission == true">
                and (
                    <!-- 本身就有发现隐患数但是还未闭环的检查 -->
                    ((cl.looped = 0 or cl.looped is null) and cw.work_type_id = 8 and cw.value is not null and cw.value != 0)
                    or 
                    <!-- 不是检查但是 可以看到自己的 1，4，5状态和所有人的待审核状态 2 -->
                    ( cw.work_type_id != 8 and
                                        (
						                        (
						                        cw.creator_id = #{vo.creatorId}  and  cw.work_state_id  in (1,4,5) 
						                        ) 
						                          or 
						                        (cw.work_state_id = 2)
						                 )
						                    
			          )
			           <!-- 是检查但是本事就是没有发现隐患数的，查询条件等同于非检查类型的合同执行 -->
			          or
			          (
			             cw.work_type_id = 8 
			             and  
			             (cw.value is null or cw.value = 0) 
			             and
                         (
                             (cw.creator_id = #{vo.creatorId}  and  cw.work_state_id  in (1,4,5) ) 
                              or 
                             (cw.work_state_id = 2)
                          )			           
			          )
                        
                    )                             
            </if>
            
            
		</where>
		order by cw.gmt_create desc
	</select>