1:
第一种: SELECT TO_CHAR(ADD_MONTHS(TO_DATE(CONCAT('2022','0101'), 'YYYYMMDD'), LEVEL - 1), 'mm') || '月' AS month FROM DUAL CONNECT BY LEVEL <= 12 第二种: SELECT TO_CHAR(ADD_MONTHS(DATE '20220101', LEVEL - 1), 'mm') || '月' AS month FROM DUAL CONNECT BY LEVEL <= 12 第三种: 不显示 0 SELECT TO_CHAR(ADD_MONTHS(TO_DATE(CONCAT('2023','0101'), 'YYYYMMDD'), LEVEL - 1), 'fmMonth') AS month FROM DUAL CONNECT BY LEVEL <= 12
执行结果:
2:个人使用
SELECT A.month AS 每年月份 ,nvl(B.目标值,0) AS 目标值, NVL(B.完成率,0) AS 完成率 from( SELECT TO_CHAR(ADD_MONTHS(TO_DATE(CONCAT('2022','0101'), 'YYYYMMDD'), LEVEL - 1), 'mm') || '月' AS month FROM DUAL CONNECT BY LEVEL <= 12) A left join (SELECT NVL((round(SUBSTR(STAT_TIME,5,6)/12* 100,2)),0) AS 目标值, NVL(REFORM_TASK_COM_RATE,0) AS 完成率, SUBSTR(STAT_TIME,5,6)|| '月' AS 每年月份 FROM BRAIN.DM_ZHGL_REFORM_TASK_INFO WHERE SUBSTR(STAT_TIME,1,4) = 2022) B ON A.month = B.每年月份
结果:
3:当前年下的 每个月,以及每个月份,除以12
SELECT TO_CHAR(ADD_MONTHS(TO_DATE(CONCAT(2022 ,'0101'), 'YYYYMMDD'), LEVEL - 1), 'mm') || '月' AS month, ROUND((LEVEL - 1) / 11.0 * 100, 2) AS 目标值 FROM DUAL CONNECT BY LEVEL <= 12;
结果: