统计查询sql

今天药忘吃喽~ 2021-10-01 07:24 430阅读 0赞

<select id="getStatistics" parameterType="PageParam" resultType="LonProcessVO" >
    
    		select  bo.orgname,
                    bo.pkorg,
                    cog.pkog,
                    cog.ogname,
                   count(DISTINCT t3.neeaid) AS neeaid,
                    count(DISTINCT t3.pkcus) AS people,
                    (select sum(b.balance) from biz_card b 
                     where b.pkorg=bo.pkorg) as balance,
    				(select count(*) from (select cc.pkorg
           from LON_PROCESS t
           left join cus_eval ce
             on ce.pkeval = t.pkaf
           left join cus_extend3 c3
             on c3.neeaid = ce.neeaid
           left join cus_customer cc
             on cc.pkcus = c3.pkcus
           left join (select t.pkdata, t.dataname
                        from bas_dic_data t
                       where t.pktablecode = 'association_relation'
                         and t.pkparent = '1') bdd
             on bdd.pkdata = c3.yhzgx
          
           left join sta_staff ss
             on ss.pksta = t.pksta
           left join sta_post sp
             on sp.pkpost = ss.pkpost
          where 1 = 1
            and t.pkoa = 'rcs'
            and c3.yhzgx = '10'
            and t.pkpn = '1104'
            and cc.pkorg = bo.pkorg)
    			    ) as approve,
    		(select count(*) from
    		 (select c.identificationno,
              c.pkorg,
              c.pkgz,
              floor(MONTHS_BETWEEN((sysdate),
                                   to_date(substr(to_char(c.identificationno),7,8),
                                           'yyyy-mm-dd')) / 12) as years
        					from cus_customer c
    		                 where 1 = 1
    		                   and c.pkgz = cog.pkog
    		                   and length(c.identificationno) = to_number(18)
    		                   and fn_checkidcard(c.identificationno) = 1)
    		         where years > 65) oldman,
    				  (select count(*)
    				    from cus_customer t
    				    left join cus_extend2 t2
    				      on t2.pkcus = t.pkcus
    				    left join cus_extend3 t3
    				      on t3.pkcus = t.pkcus
    				    left join bas_regionalism br
    				      on br.pkregi = t.pkregi
    				    left join cus_org_grid cog
    				      on cog.pkog = t.pkgz
    				    left join (select t.pkdata, t.dataname, t.remark
    				                 from BAS_DIC_DATA t
    				                where t.pktablecode = 'association_relation'
    				                  and t.pkparent = '1') bdd
    				      on bdd.pkdata = t3.yhzgx
    				    left join (select *
    				                 from (select t.pkcus,
    				                              t.markertype,
    				                              bdc.dataname,
    				                              t.markerdate,
    				                              row_number() over(partition by pkcus order by markerdate desc) rn
    				                         from CUS_LIST_MARKER_RECORD t
    				                         left join (select *
    				                                     from bas_dic_data bdc
    				                                    where bdc.pktablecode = 'markertype') bdc
    				                           on bdc.pkdata = t.markertype) t
    				                where t.rn = 1) rn
    				      on rn.pkcus = t.pkcus
    				   where 1 = 1
    				     and t3.neeaid is not null
    				     and t.pkorg = bo.pkorg
    				     and t3.PKPOVERTY_STATE = '1') AS pkpovertystate,
    				(select count(*)
    				  from cus_customer t
    				  left join cus_extend2 t2
    				    on t2.pkcus = t.pkcus
    				  left join cus_extend3 t3
    				    on t3.pkcus = t.pkcus
    				  left join bas_regionalism br
    				    on br.pkregi = t.pkregi
    				  left join cus_org_grid cog
    				    on cog.pkog = t.pkgz
    				  left join (select t.pkdata, t.dataname, t.remark
    				               from BAS_DIC_DATA t
    				              where t.pktablecode = 'association_relation'
    				                and t.pkparent = '1') bdd
    				    on bdd.pkdata = t3.yhzgx
    				  left join (select *
    				               from (select t.pkcus,
    				                            t.markertype,
    				                            bdc.dataname,
    				                            t.markerdate,
    				                            row_number() over(partition by pkcus order by markerdate desc) rn
    				                       from CUS_LIST_MARKER_RECORD t
    				                       left join (select *
    				                                   from bas_dic_data bdc
    				                                  where bdc.pktablecode = 'markertype') bdc
    				                         on bdc.pkdata = t.markertype) t
    				              where t.rn = 1) rn
    				    on rn.pkcus = t.pkcus
    				 where 1 = 1
    				   and t3.neeaid is not null
    				   and t.pkorg = bo.pkorg
    				   and rn.markertype = '2'
    				   ) as bluelist,
    				(select count(*)
    				  from cus_customer t
    				  left join cus_extend2 t2
    				    on t2.pkcus = t.pkcus
    				  left join cus_extend3 t3
    				    on t3.pkcus = t.pkcus
    				  left join bas_regionalism br
    				    on br.pkregi = t.pkregi
    				  left join cus_org_grid cog
    				    on cog.pkog = t.pkgz
    				  left join (select t.pkdata, t.dataname, t.remark
    				               from BAS_DIC_DATA t
    				              where t.pktablecode = 'association_relation'
    				                and t.pkparent = '1') bdd
    				    on bdd.pkdata = t3.yhzgx
    				  left join (select *
    				               from (select t.pkcus,
    				                            t.markertype,
    				                            bdc.dataname,
    				                            t.markerdate,
    				                            row_number() over(partition by pkcus order by markerdate desc) rn
    				                       from CUS_LIST_MARKER_RECORD t
    				                       left join (select *
    				                                   from bas_dic_data bdc
    				                                  where bdc.pktablecode = 'markertype') bdc
    				                         on bdc.pkdata = t.markertype) t
    				              where t.rn = 1) rn
    				    on rn.pkcus = t.pkcus
    				 where 1 = 1
    				   and t3.neeaid is not null
    				   and t.pkorg = bo.pkorg
    				   and rn.markertype = '3'
    				   ) as graylist,
    	                 (select count(*)
    	                from cus_customer t
    	                left join cus_extend2 t2
    	                  on t2.pkcus = t.pkcus
    	                left join cus_extend3 t3
    	                  on t3.pkcus = t.pkcus
    	                left join bas_regionalism br
    	                  on br.pkregi = t.pkregi
    	                left join cus_org_grid cog
    	                  on cog.pkog = t.pkgz
    	                left join (select t.pkdata, t.dataname, t.remark
    	                             from BAS_DIC_DATA t
    	                            where t.pktablecode = 'association_relation'
    	                              and t.pkparent = '1') bdd
    	                  on bdd.pkdata = t3.yhzgx
    	                left join (select *
    	                             from (select t.pkcus,
    	                                          t.markertype,
    	                                          bdc.dataname,
    	                                          t.markerdate,
    	                                          row_number() over(partition by pkcus order by markerdate desc) rn
    	                                     from CUS_LIST_MARKER_RECORD t
    	                                     left join (select *
    	                                                 from bas_dic_data bdc
    	                                                where bdc.pktablecode = 'markertype') bdc
    	                                       on bdc.pkdata = t.markertype) t
    	                            where t.rn = 1) rn
    	                  on rn.pkcus = t.pkcus
    	               where 1 = 1
    	                 and t3.neeaid is not null
    	                 and t.pkorg = bo.pkorg
    	                 and rn.markertype = '4'
    	                 )  as blacklist,
                    (select count(*)
    				  from cus_customer t
    				  left join cus_extend2 t2
    				    on t2.pkcus = t.pkcus
    				  left join cus_extend3 t3
    				    on t3.pkcus = t.pkcus
    				  left join bas_regionalism br
    				    on br.pkregi = t.pkregi
    				  left join cus_org_grid cog
    				    on cog.pkog = t.pkgz
    				  left join cus_eval e
    				    on e.neeaid = t3.neeaid
    				  left join lon_process p
    				    on p.pkaf = e.pkeval
    				  left join (select t.pkdata, t.dataname, t.remark
    				               from BAS_DIC_DATA t
    				              where t.pktablecode = 'association_relation'
    				                and t.pkparent = '1') bdd
    				    on bdd.pkdata = t3.yhzgx
    				  left join (select *
    				               from (select t.pkcus,
    				                            t.markertype,
    				                            bdc.dataname,
    				                            t.markerdate,
    				                            row_number() over(partition by pkcus order by markerdate desc) rn
    				                       from CUS_LIST_MARKER_RECORD t
    				                       left join (select *
    				                                   from bas_dic_data bdc
    				                                  where bdc.pktablecode = 'markertype') bdc
    				                         on bdc.pkdata = t.markertype) t
    				              where t.rn = 1) rn
    				    on rn.pkcus = t.pkcus
    				 where 1 = 1
    				   and t.pkorg = bo.pkorg
    				   and rn.markertype is not null and p.pkproc is null and t3.neeaid not in 
    				   (select t.neeaid from (select t.pkcus, t.markertype, bdc.dataname, t.markerdate, row_number() over(partition by t.pkcus order by t.markerdate desc) rn,
    				    e.neeaid from CUS_LIST_MARKER_RECORD t left join (select * from bas_dic_data bdc where bdc.pktablecode = 'markertype') bdc
    				     on bdc.pkdata = t.markertype left join cus_extend3 e on e.pkcus = t.pkcus) t 
    				     where t.rn = 1 and t.markertype in ('2', '3', '4'))
    				) commcuscustomerlist,
                    (select count(*) 
                       from (select c.pkorg from
                       LON_PROCESS t
                       left join cus_eval ce
                         on ce.pkeval = t.pkaf
                       left join cus_extend3 c3
                         on c3.neeaid = ce.neeaid
                       left join cus_customer c
                         on c.pkcus = c3.pkcus
                       left join (select t.pkdata, t.dataname
                                   from bas_dic_data t
                                  where t.pktablecode = 'association_relation'
                                    and t.pkparent = '1') bdd
                         on bdd.pkdata = c3.yhzgx
                       
                       left join sta_staff ss
                         on ss.pksta = t.pksta
                       left join sta_post sp
                         on sp.pkpost = ss.pkpost
                       left join cus_eval_result_marker cerm
                         on cerm.pkeval = t.pkaf
                      where 1 = 1
                        and t.pkoa = 'rcs'
                        and c3.yhzgx = '10'
                        and t.pkpn = '1105'
                        and t.pkstate = '1101' 
                        and c.pkorg = bo.pkorg
                     )
                      ) lonprocessnopasslistcount,
                    (select count(*) 
                    from (select c.pkorg  
                       from LON_PROCESS t
                       left join cus_eval ce
                         on ce.pkeval = t.pkaf
                       left join cus_extend3 c3
                         on c3.neeaid = ce.neeaid
                       left join cus_customer c
                         on c.pkcus = c3.pkcus
                       left join (select t.pkdata, t.dataname
                                   from bas_dic_data t
                                  where t.pktablecode = 'association_relation'
                                    and t.pkparent = '1') bdd
                         on bdd.pkdata = c3.yhzgx
                       left join sta_staff ss
                         on ss.pksta = t.pksta
                       left join sta_post sp
                         on sp.pkpost = ss.pkpost
                       left join cus_eval_result_marker cerm
                         on cerm.pkeval = t.pkaf
                      where 1 = 1
                        and t.pkoa = 'rcs'
                        and c3.yhzgx = '10'
                        and t.pkpn = '1105'
                        and t.pkstate = '1103'
                        and c.pkorg = bo.pkorg
                  )
                        ) lonprocesspasslistcount,
                        ( select count(*)
                    from cus_eval e
                    left join cus_extend3 ce
                      on e.neeaid = ce.neeaid
                    left join cus_customer c
                      on c.pkcus = ce.pkcus  
                             inner join (select *
                       from (select p.pkproc,
                                    p.pkaf,
                                    p.pkpn,
                                    p.pkstate,
                                    p.rstate,
                                    row_number() OVER(PARTITION BY p.pkaf ORDER BY p.pkpn,p.rcreationtime desc) as rn
                               from lon_process p
                              where p.pkpn in ('1103')
                              ) ce
                      where ce.rn = '1') p
    		        on p.pkaf = e.pkeval)  custobesigningdatacount,
    		       (select count(*)
    			  from cus_eval e
    			  left join cus_extend3 ce
    			    on e.neeaid = ce.neeaid
    			  left join cus_customer c
    			    on c.pkcus = ce.pkcus
    	               inner join (select *
    	                   from (select p.pkproc,
    	                                p.pkaf,
    	                                p.pkpn,
    	                                p.pkstate,
    	                                p.rstate,
    	                                row_number() OVER(PARTITION BY p.pkaf ORDER BY p.pkpn,p.rcreationtime desc) as rn
    	                           from lon_process p
    	                          where p.pkpn in ('1103')
    	                            ) ce
    	                  where ce.rn = '1') p
    	        on p.pkaf = e.pkeval 
    	         left join (select t.pkdata, t.dataname, t.remark
    	                   from BAS_DIC_DATA t
    	                  where t.pktablecode = 'association_relation'
    	                    and t.pkparent = '1') bdd
    	        on bdd.pkdata = ce.yhzgx
    	     where  c.pkorg = bo.pkorg
    	       and nvl(p.rstate, '0') = '0'
    	       and p.pkstate in ('1101')) custobenosigningdatacount
    		  from (select distinct pkcus, neeaid, PKPOVERTY_STATE from CUS_EXTEND3) t3
    		  left join (select distinct pkcus, pkorg, PKGZ from cus_customer) c
    		    on t3.pkcus = c.pkcus
    		    left join cus_org_grid cog 
    		   on cog.pkog = c.pkgz
    		  left join bas_organization bo
    		    on c.pkorg = bo.pkorg
    		  left join (select distinct t.pkcus, t.markertype, t.neeaid
    		               from CUS_LIST_MARKER_RECORD t) clmr
    		    on clmr.pkcus = c.pkcus
    		left join cus_eval_result_marker cerm
    	     	on cerm.pkcus = c.pkcus
    	    left join biz_card b
    	     	on b.bankcard = cerm.card
    		 where 1 = 1  and  cog.pklevel = 4
    	<if test='queryParams.pkorg != null and queryParams.pkorg != ""'>
    			and c.pkorg in (select bo.pkorg
                     from bas_organization bo
                    start with bo.pkorg = #{queryParams.pkorg}
                   connect by prior bo.pkorg = bo.pkparent)
    	</if>
    	<if test='queryParams.pkog != null and queryParams.pkog != ""'>
    		and c.pkgz = #{queryParams.pkog}
    	</if>
    		GROUP BY 
    		bo.orgname,bo.pkorg,cog.pkog,cog.ogname
    	   offset ${startNumber} rows fetch next ${rows} rows only
    </select>

效果图

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzQ5NTM5MA_size_16_color_FFFFFF_t_70]

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzQ5NTM5MA_size_16_color_FFFFFF_t_70]: /images/20210920/deb8f400f2394b4ca9d1e277681d4c19.png