复杂ＳＱＬＭＡＰ

 

<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>