---创建新表：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
*/