类似这种 select 年月=开始时间年月 from 表 where 年月>=开始时间 and 年月<=结束时间 union select 年月=开始时间年月+1月 from 表 where 年月>=开始时间 and 年月<=结束时间 union select 年月=开始时间年月+2月 from 表 where 年月>=开始时间 and 年月<=结束时间
union select 年月=开始时间年月+12月 from 表 where 年月>=开始时间 and 年月<=结束时间
union 12下有点多。 可以新建一个月份表。然后 月份表left你的数据表即可。
一起学习下创建触发器:create or replace trigger biufer_tbmeetmgr_CHANGETIME before insert or update or delete of CHANGETIME on tbmeetmgr for each row begin -- 调用存储过程 hzwmeetmgr; end; 存储过程:CREATE OR REPLACE PROCEDURE hzwmeetMgr is meetId number; --会议室编号 strSta varchar2(30); --会议开始时间 strEnd varchar2(30); --会议结束时间 strCreate varchar2(30); --会议创建日期 strOver varchar2(30); --会议终止日期(循环终止时期) strOverSql varchar2(30); --存入临时表中的终止日期 varInt number; --会议创建日期到终止日期相差天数 varSubTime varchar2(30); --循环日期 varMonths number; TYPE c_time IS REF CURSOR; --创建游离标记 vrec c_time; yearY varchar2(10); --日期年部分 monthsM varchar2(10);--日期月部分(终止入库时间) begin delete tbmeetmgrinfo where 11=1 ; --先清空表中数据 commit; varInt := 0; --一次性会议记录 for varCode in (select id ,huiyishi,STARTTIME,ENDTIME,CREATETIME,STOPTIME,xunhuairiqi from tbmeetmgr where HUIYIMOSHI = 1 and to_date(stoptime,'yyyy-MM-dd') >= to_date(to_char(sysdate,'yyyy-MM-dd'),'yyyy-MM-dd')) loop strOver := varCode.Stoptime; strCreate := substr(varCode.Createtime,1,10); meetId := varCode.Huiyishi; strSta := varCode.Starttime; strEnd := varCode.Endtime; varSubTime := substr(varCode.Xunhuairiqi,3); --最后的0代表的是一次性会议、循环模式1为单周、3为每月、4为每季度 insert into tbmeetmgrinfo values(tbmeetmgrinfo_tb_sequence.nextval,meetId,strSta,strEnd,strCreate,strOver,0); commit; end loop;
--单周循环 for varCode in (select id ,huiyishi,STARTTIME,ENDTIME,CREATETIME,STOPTIME,xunhuairiqi,(( to_date(stoptime,'yyyy-mm-dd') - next_day (to_date(substr(createtime, 0, 10),'yyyy-mm-dd')-1,3))/7) resultNum from tbmeetmgr where xunhuaimoshi = 1 and to_date(stoptime,'yyyy-MM-dd') >= to_date(to_char(sysdate,'yyyy-MM-dd'),'yyyy-MM-dd')) loop varInt := ceil(varCode.Resultnum); strOver := varCode.Stoptime; strCreate := substr(varCode.Createtime,1,10); meetId := varCode.Huiyishi; strSta := varCode.Starttime; strEnd := varCode.Endtime; varSubTime := substr(varCode.Xunhuairiqi,3); OPEN vrec for SELECT to_char(next_day(to_date(strCreate,'yyyy-mm-dd')-1,ceil(varSubTime)+1)+(rownum-1)*7 , 'yyyy-MM-dd') from dual connect by rownum<=varInt ; LOOP FETCH vrec INTO strOverSql; --入库终止日期 exit when vrec%notfound; --dbms_output.put_line('----+++++单周循环日期++++++-----:'||strOverSql); insert into tbmeetmgrinfo values(tbmeetmgrinfo_tb_sequence.nextval,meetId,strSta,strEnd,strCreate,strOverSql,1); commit; end loop; end loop;
--每月循环
for varCodeMonths in(select id ,huiyishi,STARTTIME,ENDTIME,CREATETIME,STOPTIME, Xunhuairiqi,ceil(months_between(to_date(stoptime,'yyyy-mm-dd'), to_date(substr(createtime, 0, 10),'yyyy-mm-dd'))) months from tbmeetmgr where xunhuaimoshi = 3 and to_date(stoptime,'yyyy-MM-dd') >= to_date(to_char(sysdate,'yyyy-MM-dd'),'yyyy-MM-dd')) loop strOver := varCodeMonths.Stoptime; strCreate := substr(varCodeMonths.Createtime,1,10); meetId := varCodeMonths.Huiyishi; strSta := varCodeMonths.Starttime; strEnd := varCodeMonths.Endtime; varMonths := varCodeMonths.Months; varSubTime := substr(varCodeMonths.Xunhuairiqi,3,4); --取得开会时间(具体哪一日) yearY := substr(strCreate,1,8); monthsM := yearY||varSubTime ; OPEN vrec for SELECT to_char(add_months(to_date(monthsM,'yyyy-MM-dd'),+(rownum-1)), 'yyyy-mm-dd') from dual connect by rownum<= ceil(varMonths) ; LOOP FETCH vrec INTO strOverSql; --入库终止日期 exit when vrec%notfound; --dbms_output.put_line('月度时间:'||strOverSql); insert into tbmeetmgrinfo values(tbmeetmgrinfo_tb_sequence.nextval,meetId,strSta,strEnd,strCreate,strOverSql,3); commit; end loop; end loop;
--每季度循环 for varCodeMonths in(select id ,huiyishi,STARTTIME,ENDTIME,CREATETIME,STOPTIME, Xunhuairiqi,ceil((to_date(stoptime,'yyyy-mm-dd')-to_date(substr(createtime, 0, 10),'yyyy-mm-dd'))/90) months from tbmeetmgr where xunhuaimoshi = 4 and to_date(stoptime,'yyyy-MM-dd') >= to_date(to_char(sysdate,'yyyy-MM-dd'),'yyyy-MM-dd')) loop strOver := varCodeMonths.Stoptime; strCreate := substr(varCodeMonths.Createtime,1,10); meetId := varCodeMonths.Huiyishi; strSta := varCodeMonths.Starttime; strEnd := varCodeMonths.Endtime; varMonths := varCodeMonths.Months; varSubTime := substr(varCodeMonths.Xunhuairiqi,3,7); --取得开会时间(具体哪一日) yearY := substr(strCreate,1,5); monthsM := yearY||varSubTime ; OPEN vrec for SELECT to_char(add_months(to_date(monthsM,'yyyy-MM-dd'),+(rownum-1)*3), 'yyyy-mm-dd') from dual connect by rownum<= ceil(varMonths) ; LOOP FETCH vrec INTO strOverSql; --入库终止日期 exit when vrec%notfound; --dbms_output.put_line('季度循环日期:'||strOverSql); insert into tbmeetmgrinfo values(tbmeetmgrinfo_tb_sequence.nextval,meetId,strSta,strEnd,strCreate,strOverSql,4); commit; end loop; end loop;
end;
谢谢亲 我已经弄好了!我的做法是 :WITH TMP_DAY AS (SELECT TO_CHAR(ADD_MONTHS(TO_DATE(to_char(sysdate - interval '3' year,'yyyy-MM'),'yyyy-MM'), ROWNUM), 'yyyy-MM') PAYMENTTIME FROM DUAL CONNECT BY ROWNUM <= 36) 这个产生月份 sysdate - interval '3' year 这个的意思是当前系统时间扣除3年 <= 36 是循36个月 3年 然后左连接:SELECT '固定资产' as ITEM, C.STATUS, C.dept_id as REIM_TARGET_DEPT, C.target_id as REIM_TARGET_ID, C.target_type as REIM_TARGET, A.PAYMENTTIME, C.monthamount as AMOUNT, C.creatuser as createuser, C.creattime as createtime, uus.realname FROM TMP_DAY A LEFT JOIN zy_fix_asset C ON 1 = 1 LEFT JOIN zy_users uus on C.creatuser = uus.id where to_date(A.PAYMENTTIME,'yyyy-MM') between to_date(to_char(c.startmonth,'yyyy-MM'),'yyyy-MM') and to_date(to_char(c.endmonth,'yyyy-MM'),'yyyy-MM');这样构造数据表 就可以了
select 年月=开始时间年月 from 表 where 年月>=开始时间 and 年月<=结束时间
union
select 年月=开始时间年月+1月 from 表 where 年月>=开始时间 and 年月<=结束时间
union
select 年月=开始时间年月+2月 from 表 where 年月>=开始时间 and 年月<=结束时间
union
select 年月=开始时间年月+12月 from 表 where 年月>=开始时间 and 年月<=结束时间
可以新建一个月份表。然后 月份表left你的数据表即可。
一起学习下创建触发器:create or replace trigger biufer_tbmeetmgr_CHANGETIME
before insert or update or delete
of CHANGETIME
on tbmeetmgr
for each row
begin
-- 调用存储过程
hzwmeetmgr;
end;
存储过程:CREATE OR REPLACE PROCEDURE hzwmeetMgr is
meetId number; --会议室编号
strSta varchar2(30); --会议开始时间
strEnd varchar2(30); --会议结束时间
strCreate varchar2(30); --会议创建日期
strOver varchar2(30); --会议终止日期(循环终止时期)
strOverSql varchar2(30); --存入临时表中的终止日期
varInt number; --会议创建日期到终止日期相差天数
varSubTime varchar2(30); --循环日期
varMonths number;
TYPE c_time IS REF CURSOR; --创建游离标记
vrec c_time;
yearY varchar2(10); --日期年部分
monthsM varchar2(10);--日期月部分(终止入库时间)
begin
delete tbmeetmgrinfo where 11=1 ; --先清空表中数据
commit;
varInt := 0;
--一次性会议记录
for varCode in (select id ,huiyishi,STARTTIME,ENDTIME,CREATETIME,STOPTIME,xunhuairiqi from tbmeetmgr
where HUIYIMOSHI = 1 and to_date(stoptime,'yyyy-MM-dd') >=
to_date(to_char(sysdate,'yyyy-MM-dd'),'yyyy-MM-dd'))
loop
strOver := varCode.Stoptime;
strCreate := substr(varCode.Createtime,1,10);
meetId := varCode.Huiyishi;
strSta := varCode.Starttime;
strEnd := varCode.Endtime;
varSubTime := substr(varCode.Xunhuairiqi,3);
--最后的0代表的是一次性会议、循环模式1为单周、3为每月、4为每季度
insert into tbmeetmgrinfo values(tbmeetmgrinfo_tb_sequence.nextval,meetId,strSta,strEnd,strCreate,strOver,0);
commit;
end loop;
--单周循环
for varCode in (select id ,huiyishi,STARTTIME,ENDTIME,CREATETIME,STOPTIME,xunhuairiqi,(( to_date(stoptime,'yyyy-mm-dd') - next_day
(to_date(substr(createtime, 0, 10),'yyyy-mm-dd')-1,3))/7) resultNum from tbmeetmgr
where xunhuaimoshi = 1 and to_date(stoptime,'yyyy-MM-dd') >=
to_date(to_char(sysdate,'yyyy-MM-dd'),'yyyy-MM-dd'))
loop
varInt := ceil(varCode.Resultnum);
strOver := varCode.Stoptime;
strCreate := substr(varCode.Createtime,1,10);
meetId := varCode.Huiyishi;
strSta := varCode.Starttime;
strEnd := varCode.Endtime;
varSubTime := substr(varCode.Xunhuairiqi,3);
OPEN vrec for SELECT to_char(next_day(to_date(strCreate,'yyyy-mm-dd')-1,ceil(varSubTime)+1)+(rownum-1)*7 , 'yyyy-MM-dd')
from dual connect by rownum<=varInt ;
LOOP
FETCH vrec INTO strOverSql; --入库终止日期
exit when vrec%notfound;
--dbms_output.put_line('----+++++单周循环日期++++++-----:'||strOverSql);
insert into tbmeetmgrinfo values(tbmeetmgrinfo_tb_sequence.nextval,meetId,strSta,strEnd,strCreate,strOverSql,1);
commit;
end loop;
end loop;
--每月循环
for varCodeMonths in(select id ,huiyishi,STARTTIME,ENDTIME,CREATETIME,STOPTIME,
Xunhuairiqi,ceil(months_between(to_date(stoptime,'yyyy-mm-dd'),
to_date(substr(createtime, 0, 10),'yyyy-mm-dd'))) months from tbmeetmgr
where xunhuaimoshi = 3 and to_date(stoptime,'yyyy-MM-dd') >=
to_date(to_char(sysdate,'yyyy-MM-dd'),'yyyy-MM-dd'))
loop
strOver := varCodeMonths.Stoptime;
strCreate := substr(varCodeMonths.Createtime,1,10);
meetId := varCodeMonths.Huiyishi;
strSta := varCodeMonths.Starttime;
strEnd := varCodeMonths.Endtime;
varMonths := varCodeMonths.Months;
varSubTime := substr(varCodeMonths.Xunhuairiqi,3,4); --取得开会时间(具体哪一日)
yearY := substr(strCreate,1,8);
monthsM := yearY||varSubTime ;
OPEN vrec for SELECT to_char(add_months(to_date(monthsM,'yyyy-MM-dd'),+(rownum-1)), 'yyyy-mm-dd')
from dual connect by rownum<= ceil(varMonths) ;
LOOP
FETCH vrec INTO strOverSql; --入库终止日期
exit when vrec%notfound;
--dbms_output.put_line('月度时间:'||strOverSql);
insert into tbmeetmgrinfo values(tbmeetmgrinfo_tb_sequence.nextval,meetId,strSta,strEnd,strCreate,strOverSql,3);
commit;
end loop;
end loop;
--每季度循环
for varCodeMonths in(select id ,huiyishi,STARTTIME,ENDTIME,CREATETIME,STOPTIME,
Xunhuairiqi,ceil((to_date(stoptime,'yyyy-mm-dd')-to_date(substr(createtime, 0, 10),'yyyy-mm-dd'))/90) months
from tbmeetmgr where xunhuaimoshi = 4 and to_date(stoptime,'yyyy-MM-dd') >=
to_date(to_char(sysdate,'yyyy-MM-dd'),'yyyy-MM-dd'))
loop
strOver := varCodeMonths.Stoptime;
strCreate := substr(varCodeMonths.Createtime,1,10);
meetId := varCodeMonths.Huiyishi;
strSta := varCodeMonths.Starttime;
strEnd := varCodeMonths.Endtime;
varMonths := varCodeMonths.Months;
varSubTime := substr(varCodeMonths.Xunhuairiqi,3,7); --取得开会时间(具体哪一日)
yearY := substr(strCreate,1,5);
monthsM := yearY||varSubTime ;
OPEN vrec for SELECT to_char(add_months(to_date(monthsM,'yyyy-MM-dd'),+(rownum-1)*3), 'yyyy-mm-dd')
from dual connect by rownum<= ceil(varMonths) ;
LOOP
FETCH vrec INTO strOverSql; --入库终止日期
exit when vrec%notfound;
--dbms_output.put_line('季度循环日期:'||strOverSql);
insert into tbmeetmgrinfo values(tbmeetmgrinfo_tb_sequence.nextval,meetId,strSta,strEnd,strCreate,strOverSql,4);
commit;
end loop;
end loop;
end;
ROWNUM),
'yyyy-MM') PAYMENTTIME
FROM DUAL
CONNECT BY ROWNUM <= 36) 这个产生月份 sysdate - interval '3' year 这个的意思是当前系统时间扣除3年 <= 36 是循36个月 3年 然后左连接:SELECT '固定资产' as ITEM,
C.STATUS,
C.dept_id as REIM_TARGET_DEPT,
C.target_id as REIM_TARGET_ID,
C.target_type as REIM_TARGET,
A.PAYMENTTIME,
C.monthamount as AMOUNT,
C.creatuser as createuser,
C.creattime as createtime,
uus.realname
FROM TMP_DAY A
LEFT JOIN zy_fix_asset C ON 1 = 1
LEFT JOIN zy_users uus on C.creatuser = uus.id where to_date(A.PAYMENTTIME,'yyyy-MM') between to_date(to_char(c.startmonth,'yyyy-MM'),'yyyy-MM') and to_date(to_char(c.endmonth,'yyyy-MM'),'yyyy-MM');这样构造数据表 就可以了