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>