连表查询的SQL优化

先连表后求和：

<select id="getChargeHallPageList"  resultType="com.yiju.modules.chargehall.pojo.entity.ChargeHallVo">
        SELECT
        t1.*,
        CONCAT( t2.structure_full_name, '-', t1.cg_room_name ) baseRoomName,
        SUM(t3.arrears_amount) totalArrearsAmount
        FROM
        charge_base_room t1
        LEFT JOIN charge_base_structure t2 ON t1.structure_id=t2.structure_id
        LEFT JOIN charge_bill t3 ON t3.cg_room_id = t1.cg_room_id
        WHERE
            1 = 1
        <if test="ew.entity.baseRoomName != null and ew.entity.baseRoomName != ''">
            AND t2.structure_full_name LIKE concat('%', #{ew.entity.baseRoomName}, '%')
        </if>
        <if test="ew.entity.cgRoomName != null and ew.entity.cgRoomName != ''">
            AND t1.cg_room_name LIKE concat('%', #{ew.entity.cgRoomName}, '%')
        </if>
        <if test="ew.entity.ownerName != null and ew.entity.ownerName != ''">
            AND t1.owner_name LIKE concat('%', #{ew.entity.ownerName}, '%')
        </if>
        <if test="ew.entity.phone != null and ew.entity.phone != ''">
            AND t1.phone LIKE concat('%', #{ew.entity.phone}, '%')
        </if>
        GROUP BY t1.cg_room_id
        HAVING 1=1
        <if test="ew.entity.minArrearsAmount!= null and ew.entity.minArrearsAmount != ''">
            AND totalArrearsAmount &gt;=#{ew.entity.minArrearsAmount}
        </if>
        <if test="ew.entity.maxArrearsAmount!= null and ew.entity.maxArrearsAmount != ''">
            AND totalArrearsAmount &lt;=#{ew.entity.maxArrearsAmount}
        </if>
        <if test="ew.emptyOfWhere == false">
            ${ew.customSqlSegment}
        </if>
    </select>

连中间表求和后取字段

<select id="getChargeHallList" resultType="com.yiju.modules.chargehall.pojo.entity.ChargeHallVo">
        SELECT
        t1.*,
        CONCAT( t2.structure_full_name, '-(', t1.cg_room_name,')' ) baseRoomName,
        arrears.totalArrearsAmount
        FROM
        charge_base_room t1
        LEFT JOIN charge_base_structure t2 ON t1.structure_id=t2.structure_id
        LEFT JOIN (
            SELECT
                cb.cg_room_id,
                SUM( cb.arrears_amount ) totalArrearsAmount
            FROM
                charge_bill cb
            WHERE
                cb.arrears_amount > 0
            GROUP BY cb.cg_room_id
        ) arrears
        ON arrears.cg_room_id = t1.cg_room_id
        <where>
            <if test="ew.entity.baseRoomName != null and ew.entity.baseRoomName != ''">
                AND t2.structure_full_name LIKE concat('%', #{ew.entity.baseRoomName}, '%')
            </if>
            <if test="ew.entity.cgRoomName != null and ew.entity.cgRoomName != ''">
                AND t1.cg_room_name LIKE concat('%', #{ew.entity.cgRoomName}, '%')
            </if>
            <if test="ew.entity.ownerName != null and ew.entity.ownerName != ''">
                AND t1.owner_name LIKE concat('%', #{ew.entity.ownerName}, '%')
            </if>
            <if test="ew.entity.phone != null and ew.entity.phone != ''">
                AND t1.phone LIKE concat('%', #{ew.entity.phone}, '%')
            </if>
            <if test="ew.entity.minArrearsAmount!= null and ew.entity.minArrearsAmount != ''">
                totalArrearsAmount &gt;=#{ew.entity.minArrearsAmount}
            </if>
            <if test="ew.entity.maxArrearsAmount!= null and ew.entity.maxArrearsAmount != ''">
                totalArrearsAmount &lt;=#{ew.entity.maxArrearsAmount}
            </if>
        </where>
        <if test="ew.emptyOfWhere == false">
            ${ew.customSqlSegment}
        </if>
    </select>

房屋表有几千条数据，账单表有几万条数据，如果先联表后求和就会并出指数倍的数据。效率极低。先根据房屋id在账单表分组求和后在形成中间表根据id再联表到房屋，数据处理效率会优化。