Oracle数据库中如何获取当前年下的01- 12 个月 并用1/12 -12/12
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;
结果: