记录一次多表关联查询

使用in关联

<select id="obtainByRootCategoryCode" resultType="com.yonghui.yh.rme.srm.productcenter.dao.dto.FirmInfoSimpleDTO">
    SELECT firm_code as firmCode,firm_name as firmName
    FROM base_firm_info
    WHERE firm_code
    IN
    (SELECT firm_code from
    base_purchase_group_firm WHERE purchase_group_code IN (
    SELECT purchase_group_code FROM category_purchase_group_relation WHERE category_level=2 AND category_code IN (
    SELECT category_code FROM base_category_info WHERE parent_code IN
    <foreach collection="condition" item="item" open="(" close=")" separator=",">
        #{item}
    </foreach>
    )
    ))
</select>