mybaitis

 resultType="java.util.HashMap"
SELECT DISTINCT
    c.COMPANY_LEVEL,
    count( 1 ) over ( partition BY COMPANY_LEVEL ) AS num,
    c_b.COMPANY_ECONOMYTYPE,
    count( 1 ) over ( partition BY COMPANY_ECONOMYTYPE ) AS ECONOMYTYPEnum
FROM
    SE_COMPANY c left join SE_COMPANY_USER c_u ON c.COMPANY_CODE = c_u.COMPANY_CODE left join SYS_USER u ON c_u.USER_ID = u.ID left join SYS_ORG o ON o.CODE = c.ORG_CODE left join SE_COMPANYPEOPLE c_p ON c.COMPANY_CODE = c_p.COMPANY_CODE left join SE_COMPANY_BUSINESS c_b ON c.COMPANY_CODE = c_b.COMPANY_CODE left join SE_COMPANYTECH c_t ON c_t.COMPANY_CODE = c.COMPANY_CODE 
WHERE
     c.COMPANY_CODE in (SELECT    COMPANY_CODE FROM  SE_COMPANY START WITH COMPANY_CODE=(SELECT COMPANY_CODE from SE_COMPANY_USER WHERE USER_ID=#{id}) CONNECT BY PRIOR ID=PARENT_ID)
         and c.COMPANY_TYPE in(1,3)

            select  
            NVL2(c.IS_PROPERTY_RIGHT,c.IS_PROPERTY_RIGHT,0) as IS_PROPERTY_RIGHT,
                  c.COMPANY_CODE,
          from
              SE_COMPANY c
                    left join SE_COMPANY_USER c_u 
                            on     c.COMPANY_CODE=c_u.COMPANY_CODE 
                    left join    SYS_USER u 
                            on  c_u.USER_ID = u.ID 
                      left join SYS_ORG o 
                            on o.CODE=c.ORG_CODE
                    left join SE_COMPANYPEOPLE c_p
                        on c.COMPANY_CODE=c_p.COMPANY_CODE    
                      left join SE_COMPANY_BUSINESS c_b
                            on c.COMPANY_CODE=c_b.COMPANY_CODE 
                      left join  SE_COMPANYTECH c_t
                            on c_t.COMPANY_CODE=c.COMPANY_CODE 
            left join (select t.COMPANY_CODE, WMSYS.WM_CONCAT('（'||t.SHAREHOLDER||':'||t.HOLDING_PERCENT||';'||CONTRIBUTE_MONEY||'万元）')  OWNERSHIP_STRUCTURE 
                                                From (select
                                                ID,
                                                SHAREHOLDER,
                                                    NVL2(HOLDING_PERCENT,HOLDING_PERCENT,'0%') as HOLDING_PERCENT,
                                                        NVL2(CONTRIBUTE_MONEY,CONTRIBUTE_MONEY,0) as CONTRIBUTE_MONEY, 
                                                COMPANY_CODE,
                                                STATE
                                         from 
                                            SE_EQUITY ) t GROUP BY t.COMPANY_CODE) t on
                        t.COMPANY_CODE=c.COMPANY_CODE
                            left join   (select t.COMPANY_CODE,
　　　　　　　　　　　　　　　　　　连字符
                                                            WMSYS.WM_CONCAT('（'||t.SCHOOL_CONTRIBUTE||':'||t.CONTRIBUTE_PERCENT||';'||CONTRIBUTE_MONEY||'万元;'||CONTRIBUTE_REALMONEY||'万元;'||CURRENCY||':'||                                                        CURRENCY_MONEY||'万元;' ||'固定资产'||':'||FIX_ASSET_MONEY||'万元;'||'无形资产:'||NON_ASSET_MONEY||'万元）'
                                                            ) 
                                                        SCHOOL_STRUCTURE      FROM 
                                                        (select  s.COMPANY_CODE,s.SCHOOL_CONTRIBUTE,
                                                            NVL2(s.CONTRIBUTE_PERCENT,s.CONTRIBUTE_PERCENT,'0%') as CONTRIBUTE_PERCENT,
                                                        NVL2(s.CONTRIBUTE_MONEY,s.CONTRIBUTE_MONEY,0) as CONTRIBUTE_MONEY,
                                                        NVL2(s.CONTRIBUTE_REALMONEY,s.CONTRIBUTE_REALMONEY,0) as CONTRIBUTE_REALMONEY,
                                                        NVL2(s.CURRENCY_MONEY,s.CURRENCY_MONEY,0) as CURRENCY_MONEY,
                                                            NVL2(s.FIX_ASSET_MONEY,s.FIX_ASSET_MONEY,0) as FIX_ASSET_MONEY,
                                                                NVL2(s.NON_ASSET_MONEY,s.NON_ASSET_MONEY,0) as NON_ASSET_MONEY,
                                                         s.CURRENCY,  s.FIX_ASSET from SE_CONTRIBUTE s) 
                                                         t GROUP BY t.COMPANY_CODE) t2 
                        on t2.COMPANY_CODE=c.COMPANY_CODE
                     where
                    c.COMPANY_CODE in (SELECT    COMPANY_CODE FROM  SE_COMPANY START WITH COMPANY_CODE=(SELECT COMPANY_CODE from SE_COMPANY_USER WHERE USER_ID=#{id}) CONNECT BY PRIOR ID=PARENT_ID)
                    and c.COMPANY_TYPE in(1,3) 
        模糊查询
                       COMPANY_NAME like '%'||#{searchContent}||'%'
         foreach<foreach item="item" index="index" collection="settDate" open="(" separator=" or " close=")">
                             <choose>
                                <when test="item==1">
                                    <!-- 一年内 -->
                                   c_b.COMPANY_CREATETIME &gt; to_char(ADD_MONTHS (sysdate, -12),'YYYYMM')
                                </when>
                                <when test="item==2">
                                    <!-- 1-3年 -->
                                    (c_b.COMPANY_CREATETIME &lt;= to_char(ADD_MONTHS (sysdate, -12),'YYYYMM') and c_b.COMPANY_CREATETIME &gt; to_char(ADD_MONTHS (sysdate, -12*3),'YYYYMM'))
                                </when>
                                <when test="item==3">
                                    <!-- 3-10年 -->
                                    (c_b.COMPANY_CREATETIME &lt;= to_char(ADD_MONTHS (sysdate, -12*3),'YYYYMM') and c_b.COMPANY_CREATETIME &gt; to_char(ADD_MONTHS (sysdate, -12*10),'YYYYMM'))
                                </when>
                                <otherwise>
                                    <!-- 10年以上 -->
                                    c_b.COMPANY_CREATETIME &lt;= to_char(ADD_MONTHS (sysdate, -12*10),'YYYYMM')
                                </otherwise>
                            </choose>
                        </foreach>