62,025
社区成员
发帖
与我相关
我的任务
分享
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');
这样构造数据表 就可以了