嵌套select

 

<select id="MarketQuotations" parameterType="java.util.Map" resultType="cnzsqh.supplychain.finance.vo.MarketQuotationsVo">
    SELECT
        fp.id,
        fp. NO,
        fp.`name`,
        fp.release_scale,
        fp.raise_start,
        fp.raise_end,
        fp.buy_min_money,
        fp.buy_asc_money,
        fp.interest_start,
        fp.interest_end,
        fp.expect_yield,
        fp.buy_max_user,
        fpa.warranty_institution_name,
        fpa.trust_institution_name,
        (
            SELECT
                count(*)
            FROM
                finance_product_buy t
            WHERE
                t.finance_product_id = fp.id
        ) AS subscriptions,
        (
            fp.release_scale - (
                SELECT
                    sum(amount)
                FROM
                    finance_product_buy t
                WHERE
                    t.finance_product_id = fp.id
            )
        ) AS surplusReleaseScale
    FROM
        finance_product fp
    LEFT JOIN finance_product_audit fpa ON fp.id = fpa.id
    WHERE fpa.`status` in
    <foreach collection="map.list" item="item" index="index" open="(" close=")" separator=",">
        #{item}
    </foreach>
    AND fpa.`owner`=#{map.owner}
    <if test="map.name !=null and map.name !=''">
        AND fp.`name` LIKE CONCAT('%', #{map.name}, '%')
    </if>
    <if test="map.raiseStart !=null and map.raiseStart !=0">
        AND fp.raise_start &gt;=#{map.raiseStart}
    </if>
    <if test="map.raiseEnd !=null and map.raiseEnd !=0">
        AND fp.raise_end &lt;=#{map.raiseEnd}
    </if>
    AND fp.raise_end &gt;=unix_timestamp(now())
    ORDER BY
        raise_start DESC
</select>