复杂ＳＱＬＭＡＰ

 

<sqlMap namespace="tt">    <sql id="ips_subcount_st">        (select count(*) from         <iterate property="tableName" conjunction="union" open="(" close=")" >              select * from  $tableName[]$        </iterate> t11         where             t11.$groupBy$=t1.$groupBy$ and             serverity <![CDATA[ > ]]>${serverity_high})        as highServeCount ,                  (select count(*)  from           <iterate property="tableName" conjunction="union" open="(" close=")" >              select * from  $tableName[]$        </iterate> t11           where              t11.$groupBy$=t1.$groupBy$ and             serverity <![CDATA[ > ]]>${serverity_low} and            serverity <![CDATA[ <= ]]>${serverity_high})          as midServeCount ,                 (select count(*)  from          <iterate property="tableName" conjunction="union" open="(" close=")" >              select * from  $tableName[]$        </iterate> t11           where           t11.$groupBy$=t1.$groupBy$ and           serverity <![CDATA[ <= ]]>${serverity_low})         as lowServeCount ,    </sql>            <select id="t_Ips"  parameterClass="java.util.HashMap"         resultClass="com.***.Ips">        select         t1.neId as neId,        t2.neName as neName,        t2.sn as sn,        t3.name as ifName,        t1.srcIp as srcIp,        t1.dstIp  as dstIp,        t1.sigName as sigName,        t1.serverity as serverity,        <isEqual property="groupBy"  compareValue="ifName">            (select count(*) from             <iterate property="tableName" conjunction="union" open="(" close=")" >                  select * from  $tableName[]$            </iterate> t11             where                 t11.ifId=t1.ifId and                 serverity <![CDATA[ > ]]>${serverity_high})            as highServeCount ,                          (select count(*)  from               <iterate property="tableName" conjunction="union" open="(" close=")" >                  select * from  $tableName[]$            </iterate> t11               where                  t11.ifId=t1.ifId and                 serverity <![CDATA[ > ]]>${serverity_low} and                serverity <![CDATA[ <= ]]>${serverity_high})              as midServeCount ,                         (select count(*)  from              <iterate property="tableName" conjunction="union" open="(" close=")" >                  select * from  $tableName[]$            </iterate> t11               where               t11.ifId=t1.ifId  and               serverity <![CDATA[ <= ]]>${serverity_low})             as lowServeCount ,        </isEqual>        <isEqual property="groupBy"  compareValue="neId">            <include refid="ips_subcount_st" />        </isEqual>        <isEqual property="groupBy"  compareValue="srcIp">            <include refid="ips_subcount_st" />        </isEqual>        <isEqual property="groupBy"  compareValue="dstIp">            <include refid="ips_subcount_st" />        </isEqual>                <isNotEqual property="groupBy"  compareValue="ifName">        <isNotEqual property="groupBy"  compareValue="neId">        <isNotEqual property="groupBy"  compareValue="srcIp">        <isNotEqual property="groupBy"  compareValue="dstIp"><!-- 这个地方不这样搞一下的话，会导致resultClass中的三个属性没有值，会出错。 -->            0 as highServeCount,            0 as midServeCount,            0 as lowServeCount,        </isNotEqual>        </isNotEqual>        </isNotEqual>        </isNotEqual>                sum(t1.sponsorNum) as sponsorNum ,        sum(t1.victimNum) as victimNum,            t2.domainId as groupId,        t6.domainName as groupName,        t1.insertTstamp  as timestamp          from          <iterate property="tableName" conjunction="union" open="(" close=")" >                  select * from  $tableName[]$         </iterate> t1          left join ( select neId,neName,sn,domainId from h_db.t_NeInfo) t2 on (t1.neId=t2.neId )                               left join (select neId,ifId,name from h_db.t_IfInfo) t3 on (t1.neId=t3.neid and t1.ifId=t3.ifId)         left join (select domainId,domainName from  h_db.t_Domain) t6 on t2.domainId=t6.domainId         <dynamic prepend="WHERE">            <isGreaterThan prepend="AND" property="neId" compareValue="0">                 t1.neId=#neId:INTEGER#             </isGreaterThan>                     <isGreaterThan prepend="AND" property="groupId" compareValue="0">                 t2.domainId=#groupId:INTEGER#             </isGreaterThan>             <isNotEmpty prepend="AND" property="ifName">                 t3.name=#ifName#              </isNotEmpty>                    <isNotEmpty prepend="AND" property="neList">                 t1.neId in                  <iterate property="neList" conjunction="," open="(" close=")" >                   #neList[]#                  </iterate>            </isNotEmpty>            <isNotEmpty prepend="AND" property="groupList">                 t2.domainId in                  <iterate property="groupList" conjunction="," open="(" close=")" >                   #groupList[]#                  </iterate>            </isNotEmpty>                <isNotEmpty prepend="AND" property="beginTime">            t1.insertTstamp<![CDATA[ >= ]]>#beginTime:TIMESTAMP#             </isNotEmpty>            <isNotEmpty prepend="AND" property="endTime">            t1.insertTstamp<![CDATA[ <= ]]>#endTime:TIMESTAMP#             </isNotEmpty>        </dynamic>        <isNotEmpty  property="groupBy">            group by $groupBy$        </isNotEmpty>        <isNotEmpty  property="orderBy">             order by  $orderBy$         </isNotEmpty>        <isGreaterThan  property="topN" compareValue="0">            limit #topN:INTEGER#        </isGreaterThan>    </select></sqlMap>