获取年份
SELECT year
FROM ( SELECT LPAD (LEVEL, 4, 0) AS year
FROM DUAL
CONNECT BY LEVEL <= TO_NUMBER (TO_CHAR (SYSDATE, 'yyyy')))
WHERE year > 2014;
执行的结果为下图
获取月份
SELECT LPAD (LEVEL, 2, 0) AS month
FROM DUAL
CONNECT BY LEVEL < 13;
执行的结果为下图
获取一段时间的年月份
SELECT year || '-' || month AS ym
FROM (SELECT year
FROM ( SELECT LPAD (LEVEL, 4, 0) AS year
FROM DUAL
CONNECT BY LEVEL <= TO_NUMBER (TO_CHAR (SYSDATE, 'yyyy')))
WHERE year > 2014),
( SELECT LPAD (LEVEL, 2, 0) AS month
FROM DUAL
CONNECT BY LEVEL < 13)
WHERE 1 = 1
执行的结果为下图