时间戳

首页 > 解决方案 > oracle月年临时表

oracle - oracle月年临时表

问题描述

我正在尝试创建一个可以在计算中关联的月、年临时表，但是我遇到了一些问题。由于限制，我无法创建全局临时表，必须依赖以下类型的查询。

WITH Months AS
(
SELECT LEVEL -1 AS ID
FROM DUAL
CONNECT BY LEVEL <=264
)
(SELECT 
ROWNUM AS MO_SYS_ID,
TO_CHAR(ADD_MONTHS(TO_DATE('01/01/1999', 'DD/MM/YY'), ID), 'YYYY'||'MM') AS MO_NM,
TO_CHAR(ADD_MONTHS(TO_DATE('01/01/1999', 'DD/MM/YY'), ID), 'MON') AS MO_ABBR_NM,
TO_CHAR(ADD_MONTHS(TO_DATE('01/01/1999', 'DD/MM/YY'), ID), 'MONTH') AS MO_FULL_NM,
TO_CHAR(ADD_MONTHS(TO_DATE('01/01/1999', 'DD/MM/YY'), ID), 'MM')AS MO_NBR,
TO_CHAR(ADD_MONTHS(TO_DATE('01/01/1999', 'DD/MM/YY'), ID), 'YYYY') AS YR_NBR 
from Months;

我真正需要做的是将它插入到我可以回忆的临时表中。不幸的是，我也没有可以从其他表中使用的任何字段。我需要它显示自 1999 年以来的 264 个月。

谢谢

标签： oraclecalendar

解决方案

您可以计算表表达式中的日期列，如下所示：

WITH Months AS (
    SELECT LEVEL -1 AS ID, ADD_MONTHS(TO_DATE('01/01/1999', 'DD/MM/YY'), LEVEL -1) as dt
    FROM DUAL
    CONNECT BY LEVEL <=264
    )
SELECT *
from Months

如果您尝试创建日期范围，您可以这样做：

WITH Months AS (
    SELECT LEVEL -1 AS ID
         , ADD_MONTHS(TO_DATE('01/01/1999', 'DD/MM/YY'), LEVEL -1) as start_dt
         , ADD_MONTHS(TO_DATE('01/01/1999', 'DD/MM/YY'), LEVEL ) as end_dt
    FROM DUAL
    CONNECT BY LEVEL <=264
    )
SELECT *
from yourtable t
inner join Months m on t.somecol >= m.start_dt and  t.somecol < m.end_dt

推荐阅读