新增客户数统计

                  select s.yf 月份, s.xzkh 新增客户, s.grkh 个人客户, s.jgkh 机构客户
                    from 
                    (
                    select to_char(to_date(a.yf, 'yyyymm'), 'yyyy-mm') yf,
                                 to_char((case
                                           when a.yf = b.yf then
                                            a.xzkh + b.rs + 4
                                           else
                                            a.xzkh
                                         end),
                                         'fm99999') xzkh,
                                 case
                                   when a.yf = b.yf then
                                    a.grkh + b.rs
                                   else
                                    a.grkh
                                 end grkh,
                                 case
                                   when a.yf = b.yf then
                                    a.jgkh + 4
                                   else
                                    a.jgkh
                                 end jgkh
                            from (select floor(kh.sctzrq / 100) yf,
                                         count(1)-sum(case
                                               when kh.khlx = 2 and kh.id  in(select t2.id from tkhxx t1,tkhxx t2 where t1.khlx=1 and t2.khlx=2 and  t1.zjlb=t2.fxrzjlx and fn_decrypt(t1.zjbh)=t2.fxrzjbh ) then
                                                1
                                               else
                                                0
                                             end) xzkh,
                                         sum(case
                                               when kh.khlx = 0 then
                                                1
                                               else
                                                0
                                             end) grkh,
                                         sum(case
                                               when kh.khlx = 1 then
                                                1
                                               else
                                                0
                                             end)+sum(case
                                               when kh.khlx = 2 and kh.id not in(select t2.id from tkhxx t1,tkhxx t2 where t1.khlx=1 and t2.khlx=2 and  t1.zjlb=t2.fxrzjlx and fn_decrypt(t1.zjbh)=t2.fxrzjbh ) then
                                                1
                                               else
                                                0
                                             end) jgkh
                                  
                                    from tkhxx kh
                                   where kh.sctzrq is not null
                                     and kh.khzt = 1
                                     and floor(kh.sctzrq / 100) >= 201901
                                     and floor(kh.sctzrq / 100) <= 201912
                                     and kh.khlx is not null
                                  
                                   group by floor(kh.sctzrq / 100)) a,
                                 khtj b
                           where a.yf = b.yf(+)
                          
                          union
                          
                          select null yf,
                                 to_char(sum(c.xzkh), 'fm99999') xzkh,
                                 sum(grkh) grkh,
                                 sum(jgkh) jgkh
                            from --order by floor(kh.sctzrq/100)
                                 (select a.yf yf,
                                         case
                                           when a.yf = b.yf then
                                            a.xzkh + b.rs + 4
                                           else
                                            a.xzkh
                                         end xzkh,
                                         case
                                           when a.yf = b.yf then
                                            a.grkh + b.rs
                                           else
                                            a.grkh
                                         end grkh,
                                         case
                                           when a.yf = b.yf then
                                            a.jgkh + 4
                                           else
                                            a.jgkh
                                         end jgkh
                                    from (select floor(kh.sctzrq / 100) yf,
                                                 count(1)-sum(case
                                               when kh.khlx = 2 and kh.id  in(select t2.id from tkhxx t1,tkhxx t2 where t1.khlx=1 and t2.khlx=2 and  t1.zjlb=t2.fxrzjlx and fn_decrypt(t1.zjbh)=t2.fxrzjbh ) then
                                                1
                                               else
                                                0
                                             end) xzkh,
                                                 sum(case
                                                       when kh.khlx = 0 then
                                                        1
                                                       else
                                                        0
                                                     end) grkh,
                                                 sum(case
                                                       when kh.khlx = 1 then
                                                        1
                                                       else
                                                        0
                                                     end)+sum(case
                                               when kh.khlx = 2 and kh.id not in(select t2.id from tkhxx t1,tkhxx t2 where t1.khlx=1 and t2.khlx=2 and  t1.zjlb=t2.fxrzjlx and fn_decrypt(t1.zjbh)=t2.fxrzjbh ) then
                                                1
                                               else
                                                0
                                             end)  jgkh
                                          
                                            from tkhxx kh
                                            where kh.sctzrq is not null
                                             and kh.khzt = 1
                                             and kh.khlx is not null
                                             and floor(kh.sctzrq / 100) >= 201901  
                                             and floor(kh.sctzrq / 100) <= 201912
                                           group by floor(kh.sctzrq / 100)) a,
                                           khtj b
                                   where a.yf = b.yf(+)) c
                          union
                          select null yf,
                                 '去年新增客户:' || to_char(count(1), 'fm99999') xzkh,
                                 null grkh,
                                 null jgkh
                            from tkhxx kh
                           where kh.sctzrq is not null
                             and kh.khzt = 1
                             and floor(kh.sctzrq / 100) >= 201801
                             and floor(kh.sctzrq / 100) <= 201812   ) s
                   order by s.yf