mybatis多表聯合查詢的寫法

猝死的路上發表於2024-04-19
MyBatis

xml

<select id="getUnitUserComsumes"  parameterType="com.zbkj.common.request.UnitUserComsumeRequest"
                    resultType="java.util.List" resultMap="itemResultMap">
        select znu.real_name,ze.enterprise_name,zd.name,zc.canteen_name,ub.pay_code,ub.number,ub.create_time
        from eb_user_bill ub
        inner join zn_enterprise_user znu
                on znu.uid = ub.uid
        left join zn_enterprise ze
                on znu.enterprise_id = ze.id
        left join zn_canteen zc
                on zc.enterprise_id = ze.id
        left join zn_device zd
                on zd.canteen_id = zc.id
        where 1=1 and ub.pm = 0
        <if test="request.deviceId != null and request.deviceId !=0">
            and zd.id = #{request.deviceId}
        </if>

        <if test="request.canteenId != null and request.canteenId !=0">
            and zc.id = #{request.canteenId}
        </if>

        <if test="request.enterpriseId != null and request.enterpriseId !=0">
            and ze.id = #{request.enterpriseId}
        </if>

        <if test="request.startTime !=null and request.startTime != ''">
            and ub.create_time <![CDATA[ >= ]]> #{request.startTime}
        </if>

        <if test="request.endTime !=null and request.endTime != ''">
            and ub.create_time <![CDATA[ <= ]]> #{request.endTime}
        </if>
        order by ub.create_time desc
    </select>

Mapper類

List<UnitUserComsumeResponse> getUnitUserComsumes(@Param("request") UnitUserComsumeRequest request);

判斷條件需要帶上 Mapper類中方法簽名的名稱

相關文章