---创建新表：1：动态

／＊

表需求：展示不包含当前月之前３５个月的起租金额，第３５个月在表中展示为：“第一个月”，依此类推

字段名                                      备注

RN                                            序号

GROUPID                                  门店ID

BTYPE3NAME                            业务品种

PRODUCTTYPENAME                产品类型       

月                                               起租金额

DT                                             数据更新时间

＊／

create table Current_month_groupDatas  as

select rownum rn,t.*, sysdate as dt from(

select * from (

select sum(a.totalRentSum) totalRentSum,ceil(months_between(sysdate, to_date(substr(a.CONFIRMDATE,0,7), 'yyyy/mm')))-1 ny,a.GROUPID GROUPID,

a.btype3name btype3name,a.productTypeName productTypeName from

(select  

 plir.GROUPID, plir.groupName,

plir.btype3name,plir.productTypeName,plir.totalRentSum,plir.CONFIRMDATE

  from PROJECT_LATEST_INFO_RELEASE plir where plir.CONFIRMDATE is not null

  ) a  group by substr(a.CONFIRMDATE,0,7),a.GROUPID,

a.btype3name,a.productTypeName

 )

  pivot (

   sum(totalRentSum) for ny 

   in(

       35 as "第一月",34 as "第二月",33 as "第三月", 32 as "第四月", 31 as "第五月", 

       30 as "第六月",29 as "第七月",28 as "第八月", 27 as "第九月", 26 as "第十月", 

       25 as "第十一月",24 as "第十二月",23 as "第十三月",22 as "第十四月",21 as "第十五月", 

       20 as "第十六月",19 as "第十七月",18 as "第十八月",17 as "第十九月",16 as "第二十号月", 

       15 as "第二十一月",14 as "第二十二月",13 as "第二十三月",12 as "第二十四月",11 as "第二十五月", 

       10 as "第二十六月",9 as "第二十七月",8 as "第二十八月",7 as "第二十九月",6 as "第三十月", 

       5 as "第三十一月",4 as "第三十二月",3 as "第三十三月",2 as "第三十四月",1 as "第三十五月"

  )

  )

  ) t

  /*

  select ceil(months_between(sysdate, to_date('2021/02/01', 'yyyy/mm/dd'))) from dual

   select * from Current_month_groupDatas

  */