ibatis 动态sql-热谱图（一）

这两天在对数据库的一个表分表：大体功能是做热谱图是有两张表，一张是：area_guest（每天有大量数据会传进来，要分表，大概是3个月要分一次比如area_guest201501，area_guest201504。。。。。以此类推），一张是area_dictionary （主要是商家的区域信息）
原sql是这样的：

<select id="getAreaGustByParam" resultMap="ibatorgenerated_BaseResultMap" parameterClass="java.util.HashMap" >
    select * from  area_guest
    where area_id in (select ad.id from area_dictionary ad where ad.mall_id=#mallId:INTEGER# and floor_code=#nowFloor:INTEGER#)
     <isNotNull  property="startTime" >
     and start_time &gt;=#startTime:TIMESTAMP#
    </isNotNull>
    <isNotNull property="endTime">
    and end_time &lt;=#endTime:TIMESTAMP#
    </isNotNull>
 </select>

分表后想写成的效果是这样：

select area_id,area_guest_num from  area_guest201504
LEFT JOIN    area_dictionary ad
on   area_guest.area_id=ad.id
AND ad.mall_id=1
AND ad.floor_code=1            
and area_guest201504.start_time >="2015-06-14 00:00:00"                     
and area_guest201504.end_time <="2015-07-01 12:00:00"            
union    
select area_id,area_guest_num from  area_guest201507
LEFT JOIN    area_dictionary ad
on   area_guest201507.area_id=ad.id
AND ad.mall_id=1
AND ad.floor_code=1          
and area_guest201507.start_time >="2015-06-14 00:00:00"                     
and area_guest201507.end_time <="2015-07-01 12:00:00" 

问题来了，这是一个静态sql，怎么变成动态的，经过一番努力结果如下：

<select id="getAreaGustByParam2" resultMap="ibatorgenerated_BaseResultMap2" parameterClass="java.util.HashMap" >
    <dynamic>
        <iterate property="list" conjunction="union">
        select area_id,area_guest_num from area_guest$list[].tablename$ 
        LEFT JOIN    area_dictionary ad
        on   area_guest$list[].tablename$.area_id=ad.id
        <isNotNull property="list">
        AND ad.mall_id=#list[].mallId:INTEGER#                     
        </isNotNull>
        <isNotNull property="list">
        AND ad.floor_code=#list[].nowfloor:INTEGER#                   
        </isNotNull>
        <isNotNull property="list">
        and area_guest$list[].tablename$.start_time &gt;=#list[].startTime:TIMESTAMP#                     
        </isNotNull>
        <isNotNull property="list">
        and area_guest$list[].tablename$.end_time &lt;=#list[].endTime:TIMESTAMP# 
        </isNotNull>            
        </iterate>
    </dynamic>
 </select>

还有参数是这样传的map.put(“list”,list)；